(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
VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: -1 (from 1 vote)
Be Sociable, Share!