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.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Stored Procedure Within Stored Procedure?

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 #Temp

Tara Kizer
aka tduggan
Go to Top of Page

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?
Go to Top of Page

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 Kizer
aka tduggan
Go to Top of Page

ywb
Yak Posting Veteran

55 Posts

Posted - 2005-12-19 : 18:32:58

Got it. Thanks, Tara!
Go to Top of Page

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 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
Go to Top of Page
   

- Advertisement -