Is there any way to return multiple results from a SP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Is there any way to return multiple results from a SP

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-01-04 : 09:41:32

writes "Is there any way to return multiple results from a stored procedure in sql server 2000 i.e
Can i use two EXEC stmt's within one single stored procedure.

I have tried two ways.
Using first way i am able to return resultset as well as count. The reason is the 2nd
query inside first method is static. But i need the second query to be built dynamically.
That is what i tried using second method but failed to do it.

here is the code

method-1
========
create PROCEDURE TEST_SP(@N INT OUT)
AS
DECLARE @SQL VARCHAR(100)
BEGIN
SET @SQL='SELECT * FROM DEPT'
EXEC (@SQL)
SELECT @N=COUNT(*) FROM DEPT
END

sample execution of first method
-----------------------------------
DECLARE @N INT
EXEC TEST_SP @N OUTPUT
SELECT @N

method-2
=========
create PROCEDURE TEST_SP(@N INT OUT)
AS
dECLARE @S1 VARCHAR(100)
dECLARE @S2 VARCHAR(100)
BEGIN
SET @S1='SELECT * FROM DEPT'
EXEC (@S1)
set @s2='SELECT @N=COUNT(*) FROM DEPT'
exec (@s2)
END

sample execution of second method
-----------------------------------
DECLARE @N INT
EXEC TEST_SP @N OUTPUT
SELECT @N

Error:

Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@N'.

Any answers will be appreciable.
Thanks in advance,
Regards,
Raj"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-01-04 : 10:12:41

http://www.nigelrivett.net/SQLTsql/sp_executesql.html

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-01-04 : 10:13:55

But I think what you need is

SELECT * FROM DEPT
SELECT @@ROWCOUNT

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources