Problem with output parameter - SQL Server Forums

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)

Problem with output parameter

Author

Topic

mahesh.vsp
Starting Member

5 Posts

Posted - 2006-12-28 : 01:10:27

Hi pals,

I have one doubt.

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

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-12-28 : 01:36:26

create PROCEDURE TEST_SP(@N INT OUTPUT)

madhuotp
Yak Posting Veteran

78 Posts

Posted - 2006-12-28 : 01:40:51

in the second case u have a variable in dynamic sql. so u must use sp_executesql statement instead of exec.
refer this URL ;www.sommarskog.se/dynamic_sql.html

madhu

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-12-28 : 01:45:58

A Few more corrections

SET @SQL= (SELECT * FROM DEPT -- Put a where (filter) condition else you will get all records back)
EXEC Your_SP @SQL
SELECT @N= (select COUNT(*) FROM DEPT)
END

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

sample execution of second method
-----------------------------------
DECLARE @N INT
set @n = 'your value goes here'
EXEC TEST_SP @N OUTPUT
~~SELECT @N~~

mahesh.vsp
Starting Member

5 Posts

Posted - 2006-12-28 : 03:52:12

Africa,

How can you pass a string value to the SP which is having OUT parameter of type INT.
i.e

A Few more corrections

SET @SQL= (SELECT * FROM DEPT -- Put a where (filter) condition else you will get all records back)
EXEC Your_SP @SQL
SELECT @N= (select COUNT(*) FROM DEPT)
END

What r you trying to say.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-12-28 : 08:21:10

What frontend are you using ? asp or asp.net ?



create PROCEDURE TEST_SP(@N INT OUT)
AS
DECLARE @SQL VARCHAR(100)

SET @SQL= (SELECT * FROM DEPT) -- Put a where (filter) condition else you will get all records back)
EXEC Your_SP @SQL 
SELECT @N= (select COUNT(*) FROM DEPT)
return @n

where @N is your string value.

Does this answer your question ? And what exactly are you trying to do ?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-28 : 09:11:13

I don't know why you want second query to be built dynamically...but if at all there is some valid reason for that, then it is a good candidate for sp_executesql like this:

declare @sql nvarchar(4000)
set @sql = N'select @n= count(*) from Dept'
exec sp_executesql @sql, N'@n int output', @n output

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-01-01 : 03:39:25

1 What is the point in using dynamic SQL when you are not passing object names as parameters?
2 http://www.nigelrivett.net/SQLTsql/sp_executesql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources