June 16th, 2008, 03:09 PM
|
|
Contributing User
|
|
Join Date: Jan 2008
Location: Connecticut, USA
Posts: 42
Time spent in forums: 13 h 13 m 55 sec
Reputation Power: 2
|
|
|
selecting with a temp table?
Here are some ideas, assuming I am understanding your needs right.
1. Use UNION instead of combining in a temp table. This can also be placed in a view any stored procedures can access.
Code:
SELECT key1, att1
FROM permTable1
WHERE key > xx AND date > 999999
UNION ALL
SELECT key1, att1
FROM permTable1History
WHERE key > xx AND date > 999999
2. Combine the stored procedures so this code is in the beginning of the code and then you process it without going into another stored procedure.
3. Instead of #temp_table use 2 "#"s, (##temp_table) so it's accessible from outside procedures (use with caution)
Code:
select key1, att1 into ##temp_table
from permTable1
where
key > xx
date > 999999
insert ##temp_table
select key1, att1 from permTable1History
where
key > xx
date > 999999
The procedure actually just makes and populates the table but doesn't return any values. The calling procedure will have to run the procedure to populate the temporary table like so: (let's assume the above stored procedure is called sproc_make_temp_table)
Code:
-- populate temp table
EXEC sproc_make_temp_table
-- select values (or process as necessary)
SELECT key1, att1
FROM ##temp_table
-- clean up table or it will stay there and can make a mess
DROP TABLE ##temp_table
|
Dev Articles Community Forums Sponsor:
Temp table manipulation in Stored Procedure
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
