We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Ctreating temp table inside Exec command.

raghava_dg
raghava_dg asked
on
Medium Priority
1,958 Views
Last Modified: 2007-12-19
Hai frnds,

I have used the temp table inside my Exec statement. And If I tried to re use that temp table after Exec,it gave error as #output1 (this is name of temp table) not found .

What is the solution for this.
I need to use Exec coz I need to create a dynamic query depending on the input.


Please help me on this.

My proc is as below

create procedure ParentTest1
@whereClause varchar(100)
AS
DECLARE


@sql varchar(1000)

      select @sql = "SELECT od.orderID orderDetailID,o.parentOrderID tmpParentOrderID ,o.orderID,
      od.acctMnc,od.securityMnc
      INTO #output1
      FROM tmpOrders o, tmpOrderDetail od
      WHERE o.orderID *= od.orderID"+@whereClause
      
      exec(@sql)
      select * from #output1


I think life of #output1 will end as soon as we complete Exec . Is this rt? Do u guys have any other solutions?


Thanks a lot for ur suggestion.

Thanks
Raghava
Comment
Watch Question

Commented:
That is right, an EXEC is essentially a new connection to ASE and the temp table only exists within that scope (nor can the EXEC reference temp tables created outside of it's own scope).

The general solution is to create a permanent table to pass info through.  You can provide a key value in the string you pass to exec (such as the caller's @@spid value) that is inserted as a column value in the permanent table so the caller can identify their own rows.

General flow:

create table results (spid int, result int)
go

-- get rid of any old data in the table
delete results where spid = @@spid
-- call the exec that stores values
-- in the results table
exec (
            "insert results values ("
         + str(@@spid,10,0)
         + ", 42)"
)

select * from results where spid = @@spid

Author

Commented:
thanks bret ,

but my worry in the above suggestion is .... I need to have a separate routine to purge the tmp table.
Because i will be passing "spid " after generating it by randaom genarator from my client. So each time it will be diffrent and the delete statement in my proc will not be of much help , I mean since each time new "spid " will be passed so i may not be able to delete it.

For my another technical reason due to the Java client I can not delete those records after i finesh my proceesing inside my proc coz last statement in my proc should return a resultset (i,e a select statement).
CERTIFIED EXPERT

Commented:
Please maintain open questions.

Regards-
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks bret .
One more solution is to create the temp table out side ASE and use that #temp table inside ASE. Once sp is finished temp table will be deleted automatically.

Thanks for the help

Commented:
Hi,
Another way to do it is by chainning the commands, I used it  before several times and it works great :-)

declare @sql_text varchar(255)

select @sql = "SELECT od.orderID orderDetailID,o.parentOrderID tmpParentOrderID ,o.orderID,
     od.acctMnc,od.securityMnc
     INTO #output1
     FROM tmpOrders o, tmpOrderDetail od
     WHERE o.orderID *= od.orderID"+@whereClause

select @sql_text = "select * from #output1"    

exec(@sql + @sql_text)

good luck.
iziki.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.