Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ywb
Yak Posting Veteran
55 Posts |
 Posted - 2005-12-19 : 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
38200 Posts |
Posted - 2005-12-19 : 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 #TempTara Kizeraka tduggan |
 |
|
|
ywb
Yak Posting Veteran
55 Posts |
Posted - 2005-12-19 : 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
38200 Posts |
Posted - 2005-12-19 : 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 Kizeraka tduggan |
|
|
|
ywb
Yak Posting Veteran
55 Posts |
Posted - 2005-12-19 : 18:32:58
|
| Got it. Thanks, Tara! |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2005-12-19 : 21:40:57
|
To be exact, #temp table are local to that user's connection. See BOL on temporary tablesquote:
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 |
|
|
|
|
|
|
|
|
Stored Procedure Within Stored Procedure?