Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2006
    Posts
    16

    Unanswered: Scope of temp tables across stored procedures

    I have one stored proc that creates the temp table and populates it. Once it is populated, there is another stored proc which is called from the original stored proc. The second stored proc uses the values in the temp table. After this, it transfers control back to the original stored proc and then drops the table.

    I create the temp table in the first stored proc using static sql while I use it in the second stored proc using dynamic sql.

    But I am not able to see the values in the temp table in the second stored proc.Any ideas?

    Here is the first stored proc

    create procedure sm146_temp_test_proc as
    declare @var_cnt int
    create table #temp_tab_ast(ext_sec_id varchar(50))
    exec('insert into #temp_tab_ast(ext_sec_id) select ext_sec_id from riv_stage_ast where feed_cd = ''EAGLE_IDX'' and sec_name = ''ZORAN CORP ZRAN''')
    print 'Transferring control'
    exec sm146_temp_tab_access_proc
    print 'Back here'
    drop table #temp_tab_ast


    and the called stored proc

    create procedure sm146_temp_tab_access_proc as
    declare @str varchar(50)
    exec('select '+@str+'=ext_sec_id from #temp_tab_ast')
    select @str= 'count is'+@str
    print @str

  2. #2
    Join Date
    May 2005
    Location
    Paris
    Posts
    46
    Hi,
    do you have a result like :
    Server Message: Number 208, Severity 16
    Procedure 'sp_test1', Line 6:
    #temp1 not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).
    (return status = -6)

    ??

    Please note that :

    temp tables created with # are accessible by the current session or procedure
    to share a temp table among sessions use 'create table tempdb..tablename...

    details see TSQL User's Guide, chapter 7, using temporary tables pp238 (ASE 12.5)







    Now i found something which can help you :


    Firstly I considered whether a session ID or a timestamp could be added to the name of a temporary table created in tempdb as we had discussed. As you are probably aware, select @@spid will give the spid of the current connection. Unfortunately it is not possible to use a variable within a table name and so I moved to work around the issue in another way.

    Would you requirements be met by using two separate temporary tables? You will find an example of the syntax here below:

    create table #temp1 ( col1 smallint)
    go
    create procedure sp_test1 ( @prm1 smallint )
    as
    begin
    if @prm1 = 1
    select count(*) from sysobjects
    else
    update #temp1 set col1 = 20
    end
    go


    create procedure sp_test2( @parm smallint)
    as
    begin
    create table #temp2 ( col1 smallint)
    insert into #temp2 values (10)
    insert into #temp2 values (30)
    insert into #temp2 values (40)

    exec sp_test1 @parm

    select * from #temp2
    end


    what do you think about ?

  3. #3
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Why do you need to use dynamic sql, you don't need it here?

    The problem is not in the way that you use the temp table but in the way you use dynamic sql.

    Since @str is unset
    exec('select '+@str+'=ext_sec_id from #temp_tab_ast')
    Will translate to
    exec('select =ext_sec_id from #temp_tab_ast')
    And you should get
    Incorrect syntax near '='.

  4. #4
    Join Date
    Apr 2006
    Posts
    16
    Pdreyer
    I dont get this error when I try to run that query instead it completes and shows the variable to hold no value.

    I am using dynamic sql as that temp table has no existence on the second stored proc and if I use it in static sql it would error out and not compile.

  5. #5
    Join Date
    Apr 2006
    Posts
    16

    In simple words...

    In simple words all I am trying to do is create a temporary table in one procedure, calling a second stored proc from the first one and then trying to use the contents of the temp table in the second stored proc.

    Any ideas as to how this can be done efficiently?

  6. #6
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Quote Originally Posted by memonks
    I dont get this error
    Well I executed your code and got
    Transferring control
    count is
    Back here
    Server Message: Number 257, Severity 16
    Server 'mxg2', Line 1:
    Implicit conversion from datatype 'INT' to 'VARCHAR' is not allowed. Use the CONVERT function to run this query.
    Server Message: Number 102, Severity 15
    Server 'mxg2', Line 1:
    Incorrect syntax near '='.
    (1 row affected)
    (return status = 0)

    Quote Originally Posted by memonks
    I am trying to create a temporary table in one procedure, calling a second stored proc from the first one and then trying to use the contents of the temp table in the second stored proc. Any ideas
    Just create the temp table before you create the 2nd proc e.g.
    Code:
    create table #t1 (c1 int, c2 char(1))
    go
    create proc p2 as 
    update #t1 set c2='X' where c1<4
    go
    drop table #t1
    go
    Then the rest e.g.
    Code:
    create proc p1 as
    create table #t1 (c1 int, c2 char(1))
    insert into #t1 select dbid,'D' from master..sysdatabases
    insert into #t1 select srvid,'S' from master..sysservers
    exec p2
    select c2,'count'=count(*) from #t1 group by c2
    drop table #t1
    go
    And executing it:
    Code:
    exec p1
    
    c2   count       
    --   ----------- 
    D              7 
    S              4 
    X              6 
    
    (3 rows affected)
    (return status = 0)

  7. #7
    Join Date
    Apr 2006
    Posts
    16

    thanks!

    Thanks pdreyer and everybody else. I got this working by the way you suggested and now am begining to understand the nuances of temp tables on sybase.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •