SQL Server Performance Forum – Threads Archive

Stored procedure output

Hello Everybody, I need your inputs i have to return value from as an output from sp which is not an interger. How can i do that. Can i return any value(any datatype) from stored procedure ?. Thanks Ravi K

yes possible
examples
Create Procedure GetSalesById
(
@Id int,
@Sales money OUTPUT
)
As
SELECT @Sales = sales
FROM sample
WHERE id = @Id for more information read
http://www.sql-server-performance.com/tn_stored_procedure_returns.asp

Ok. Means SP can return any data type. But return is limited to one value only

No
u can have several outout parameters see the example below
CREATE PROCEDURE titles_sum @@TITLE varchar(40) = ‘%’, @@SUM money OUTPUT ,@@TOT int OUTPUT
AS
SELECT ‘Title Name’ = title
FROM titles
WHERE title LIKE @@TITLE
SELECT @@SUM = SUM(price)
FROM titles
WHERE title LIKE @@TITLE
GO

and use Return @outputvar to retrieve output value
Madhivanan Failing to plan is Planning to fail

But we can not use multiple return statements in SP. Current me if I am wrong.
Surendra Kalekar

BOL specifies that you can have up to 2,100 parameters. Each of which can be input or output. —
Frank Kalis
SQL Server MVP
http://www.insidesql.de

quote:and use Return @outputvar to retrieve output value

You don’t need to do that.
create procedure myProc
@inputParameter inputParameterType,
@outputParameter outputParameterType
as begin
—
…do something…
—
select @outputParameter = …
—
Return returnCodeValue
go
—
declare @ReturnCode int
declare @localVar outputParameterType
—
exec @ReturnCode = myProc
@inputParameter = inputParameterValue,
@outputParemeter = @localVar output
go

Well mmarovic. If I want to retreive output parameter from my Frontend(say VB6), How can I use the code witout using Return?
Madhivanan Failing to plan is Planning to fail

Use ado command object, define parameter as output. See "Using Return Code and Output Parameters for Stored Procedures" in BOL.

Thanks everybody for the answer but my one question is unanswer. I also asked about output datatype. As per my understanding we can only return output parameter which is integer because RETURN which is function for returing value requires Arguments as Arguments expression. so sp can returns only integer. Please correct me if i am wrong. Thanks and regards
Ravi.

quote:As per my understanding we can only return output parameter which is integer because RETURN which is function for returing value requires Arguments as Arguments expression.

No, return statement returns return code value, not value of output paramter. Output parameter value is assigned by using select or set statement inside sp body and it is returned if you define variable that will recive the value of output parameter in a code executing stored procedure. Value will be returned only if you define parameter as output in sp execution code too. Please take a look at examples posted by me and dineshasanka. Also read from BOL chapter mentioned.

mmarovic is correct Test this procedure
Create procedure Test (@s varchar(10)=null output)
as
set nocount on
select @s = ‘Testing’
select @s declare @a varchar(10)
execute @a = Test
select @a Madhivanan Failing to plan is Planning to fail

This is your previous posting <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by SQL2000DBA Ok. Means SP can return any data type. But return is limited to one value only <hr height="1" noshade id="quote"></blockquote id="quote"> Now this [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by SQL2000DBA Thanks everybody for the answer but my one question is unanswer. I also asked about output datatype. As per my understanding we can only return output parameter which is integer because RETURN which is function for returing value requires Arguments as Arguments expression. so sp can returns only integer. Please correct me if i am wrong. Thanks and regards Ravi. <hr height="1" noshade id="quote"></blockquote id="quote"> <h6>Surendra Kalekar</h6>

]]>