Here's an example procedure that we might like to reuse:
CREATE PROC usp_Demo_AllAuthors as select * from pubs..authors GO
Now here's a stored procedure that uses the results of usp_Demo_AllAuthors:
CREATE proc usp_Demo_SPUser as CREATE TABLE #Authors ( au_id varchar(11) NOT NULL PRIMARY KEY CLUSTERED, au_lname varchar (40) NOT NULL , au_fname varchar (20) NOT NULL , phone char (12) NOT NULL, address varchar (40) NULL , city varchar (20) NULL , state char (2) NULL , zip char (5) NULL , contract bit NOT NULL ) -- Execute usp_Demo_AllAuthors storing the -- results in #Authors insert into #Authors exec usp_Demo_AllAuthors -- Here we use the #Authors table. This example only -- only selects from the temp table but you could do much -- more such as use a cursor on the table or join with -- other data. SELECT au_fName + ' ' + au_lname as [name] , address+', '+city+', '+state+' '+zip [Addr] from #Authors DROP TABLE #Authors GO
Do you have comments on this Ask the Expert Q&A? Let us know.
This was first published in May 2005