Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Ctreating temp table inside Exec command.

Posted on 2004-03-29
6
Medium Priority
?
1,717 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
0
Comment
Question by:raghava_dg
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 10

Expert Comment

by:bret
ID: 10704699
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

0
 

Author Comment

by:raghava_dg
ID: 10705246
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).
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 10710303
Please maintain open questions.

Regards-
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 10

Accepted Solution

by:
bret earned 150 total points
ID: 10733606
Well, that is one reason why I use the actual spid of the user process; it is easy to tell going in that if there is existing data with my own spid that it can be deleted.  (For that matter, any rows with a spid that is not currently in sysprocesses could also be deleted).

Perhaps your sproc could do this, which would both delete the records and allow the last statement to be the result set.  The temp table will automatically be cleaned up by ASE when the procedure exits.

select * into #temp from results where spid = @@spid
delete results where spid = @@spid
select * from #temp
0
 

Author Comment

by:raghava_dg
ID: 10805683
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
0
 
LVL 1

Expert Comment

by:iziki
ID: 10911503
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.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As much as Microsoft wants to kill off PST file support, just as they tried to do with public folders, there are still times when it is useful or downright necessary to export Exchange mailboxes to PST files. Thankfully, it is still possible to e…
Article by: evilrix
Looking for a way to avoid searching through large data sets for data that doesn't exist? A Bloom Filter might be what you need. This data structure is a probabilistic filter that allows you to avoid unnecessary searches when you know the data defin…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question