Capture return value of a Stored Procedure

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

General SQL Server Forums

New to SQL Server Programming

Capture return value of a Stored Procedure

Author

Topic

sura
Starting Member

10 Posts

Posted - 2011-03-17 : 00:06:53

Good day to All,

I am new to SQL Server world, working with a SQL Server Stored procedure.

Need is to get a return integer value from a Stored Procedure. I am executing the Stored Procedure using "sqlcmd" (Running from Datastage tool).

Passing necessary paramas with SQLCMD to connect DB, uname, pwd etc.

See the below EXEC which i need to execute.

"EXEC dbo.Start_Script 'INP','@OP'"

dbo.Start_Script - Stored Procedure Name
INP - Input param value.
OP - Output.

If i run the command without output param, it is given an error msg "Expecting @OP".

If i given @OP getting and error msg, trying to convert from CAHR to INT.

I am not sure how to achive my task.

It will be great if any one help me in this.

Notes: I tried a simple select statement and it is working fine. But in the return value i am getting a message (1 row affected) along with the value. Please guide me to take only the value.

Thanks in advance,
Small fish in "SQL Server Ocean"

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-03-17 : 01:04:26

"EXEC dbo.Start_Script 'INP','@OP'"

When '@OP' is in single quote, it will be a string and its value will not be substited during call.

You should use

EXEC dbo.Start_Script 'INP',@OP output

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-03-17 : 01:07:08

what is the datatype you have given to @OP parameter.
try it by declaring it a int

sura
Starting Member

10 Posts

Posted - 2011-03-17 : 01:22:57

Thanks for the reply.

Return Data type in INT.

When i tried @OP it is giving an error msg @OP is not declared. If i given it as '@OP' giving type conversion error.

Thanks

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-03-17 : 01:35:58

try executing the procedure using this syntax.when executing a procedure with OUT parameters you need to declare it and then execute

DECLARE @ID INT
SET @ID = 1
EXEC dbo.InputOutputParameter @ID OUT

SELECT @ID

sura
Starting Member

10 Posts

Posted - 2011-03-17 : 01:43:52

Hi

Thanks for your reply. I will work it out.

Is it the only way to get the return value?

Thanks

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-03-17 : 01:45:16

quote:
Originally posted by sura

Hi

Thanks for your reply. I will work it out.

Is it the only way to get the return value?

Thanks

as far as i know

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-03-17 : 03:32:12

quote:
Originally posted by sura

Thanks for the reply.

Return Data type in INT.

When i tried @OP it is giving an error msg @OP is not declared. If i given it as '@OP' giving type conversion error.

Thanks

Can you show us the code where you are getting error.

Blanchemab
Starting Member

2 Posts

Posted - 2013-04-28 : 23:11:54

unspammed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-29 : 00:23:14

quote:
Originally posted by sura

Hi

Thanks for your reply. I will work it out.

Is it the only way to get the return value?

Thanks

You can return value using RETURN statement as well inside procedure so far as value is of integer type. But general practise is to use OUTPUT parameter as suggested for value returning. RETURN is only used to return some custom execution status values.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources