How to call one stored procedure from another stored procedure without displaying previous stored procedure’s results

(SQL Server)
Had a question from a student:

Problem:
He needs to create a temporary script while they migrate from an old database to SQL Server. He needs to call a sproc (SP1) from another sproc (SP2). SP1 returns a result set; SP2 must return only a scalar value (a COUNT, an AVG).

Problematic Query:

CREATE PROC SP1
AS
SELECT au_lnameFROM authors
GO

CREATE PROC SP2
AS
DECLARE @count INT

--this is problematic because this EXEC 
--displays the results of SP2
EXEC SP1

SELECT @count=COUNT(*)FROM authorsGO
Workable Solution:

CREATE PROC SP1
AS
SELECT au_lname FROM authors
GO

CREATE PROC SP2
AS -- don't show number of rows affected, we don't need it
SET NOCOUNT ON

--create a temporary table
--for purposes of my student's issue, this is fine
--you need to be careful when creating 
--temporary tables in sprocs, though, you need to 
--remember there are performance tradeoffs
CREATE TABLE #tmp 
(   
   au_lname VARCHAR(20)
)

--do an INSERT..EXEC
INSERT #tmp (au_lname)
EXEC SP1

--display number of records in the temporary table
SELECT COUNT(*) FROM #tmp
GO

--to test, execute SP2EXEC SP2
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s