SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Capture return value of a Stored Procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sura
Starting Member

10 Posts

Posted - 03/17/2011 :  00:06:53  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
1182 Posts

Posted - 03/17/2011 :  01:04:26  Show Profile  Reply with Quote
"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
Go to Top of Page

ahmeds08
Aged Yak Warrior

India
534 Posts

Posted - 03/17/2011 :  01:07:08  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
what is the datatype you have given to @OP parameter.
try it by declaring it a int
Go to Top of Page

sura
Starting Member

10 Posts

Posted - 03/17/2011 :  01:22:57  Show Profile  Reply with Quote
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
Go to Top of Page

ahmeds08
Aged Yak Warrior

India
534 Posts

Posted - 03/17/2011 :  01:35:58  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
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
Go to Top of Page

sura
Starting Member

10 Posts

Posted - 03/17/2011 :  01:43:52  Show Profile  Reply with Quote
Hi

Thanks for your reply. I will work it out.

Is it the only way to get the return value?

Thanks

Go to Top of Page

ahmeds08
Aged Yak Warrior

India
534 Posts

Posted - 03/17/2011 :  01:45:16  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
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
Go to Top of Page

pk_bohra
Flowing Fount of Yak Knowledge

India
1182 Posts

Posted - 03/17/2011 :  03:32:12  Show Profile  Reply with Quote
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.
Go to Top of Page

Blanchemab
Starting Member

East Timor
2 Posts

Posted - 04/28/2013 :  23:11:54  Show Profile  Reply with Quote
unspammed
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/29/2013 :  00:23:14  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000