| Author |
 Topic  |
|
|
ywb
Yak Posting Veteran
50 Posts |
 Posted - 12/19/2005 : 17:25:03
|
Hi,
I have a stored procedure that contains a long and complex query and returns a list of customer information.
Now I'd like to have several other store procedures to analyze this result in different ways. For example, one stored procedure might extract the customer from a certain state, another stored procedure might return the result sorted by a different order etc. Within a stored procedure, how can I execute another stored procedure and work with the result?
W. |
|
|
tkizer
Almighty SQL Goddess
USA
28965 Posts |
Posted - 12/19/2005 : 17:37:30
|
Within a stored procedure, you can execute a stored procedure via EXEC OwnerName.StoredProcedureName. Check SQL Server Books Online for details on executing stored procedures. If the stored procedure returns a result set, then you can:
CREATE TABLE #Temp...
INSERT INTO #Temp...
EXEC dbo.SP1 @parm1 = 10
...
DROP TABLE #Temp
Tara Kizer
aka tduggan |
 |
|
|
ywb
Yak Posting Veteran
50 Posts |
Posted - 12/19/2005 : 17:44:12
|
I see... So I have to create temp table...
Would there be a problem if this stored procedure is run simultaneously by multiple users? |
|
|
|
tkizer
Almighty SQL Goddess
USA
28965 Posts |
Posted - 12/19/2005 : 17:54:22
|
In order to work with a result set of a stored procedure, yes you need to use a temporary table. # temp tables are local to that user, so it wouldn't be a problem if run by multiple users simultaneously.
Tara Kizer
aka tduggan |
|
|
|
ywb
Yak Posting Veteran
50 Posts |
Posted - 12/19/2005 : 18:32:58
|
Got it. Thanks, Tara! |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
13672 Posts |
Posted - 12/19/2005 : 21:40:57
|
To be exact, #temp table are local to that user's connection. See BOL on temporary tables
quote:
Local temporary tables have a single number sign (#) as the first character of their names; they are visible only to the current connection for the user; and they are deleted when the user disconnects from instances of Microsoft® SQL Server™ 2000.
-----------------
[KH]
Learn something new everyday |
|
|
| |
Topic |
|
Stored Procedure Within Stored Procedure?
