(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_lname
FROM 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 authors
GO
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 SP2
EXEC SP2
0 comments:
Post a Comment