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

Written by belle
Posted November 21, 2007 at 3:32 am

(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

Rating: 0.0/10 (0 votes cast)

Rating: -1 (from 1 vote)

Be Sociable, Share!