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.
Dig Deeper on SQL Server Stored Procedures
Related Q&A from Andrew Novick
Can't decide whether to use nullable fields in a table. Development expert Andrew Novick discusses what to do. Continue Reading
If you've had trouble connecting Visual Basic to SQL Server, let expert Andrew Novick offer some basic advice to get you started. Continue Reading
Development expert Andrew Novick gives you the resources to improve your stored procedure writing skills. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.