Stored Procedures & Return Values

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

SQL Server Development (2000)

Stored Procedures & Return Values

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-11-18 : 08:04:25

Greg writes "Is the integer, the only return type available from a stored procedure? Using the debugger in MS SQL 2000, the following two stored procedures fail to output the expected result type.
--------------------------------------
CREATE PROCEDURE dbo.TestDecimalOutput
{
@Temp decimal(12,6) OUTPUT
}
AS
SET @Temp = 123.456
RETURN @Temp
--------------------------------------
CREATE PROCEDURE dbo.TestVarcharOutput
{
@Temp varchar(50) OUTPUT
}
AS
SET @Temp = '123.456'
RETURN @Temp
--------------------------------------
In the first case an integer (123) is returned and in the second case failure indicating that an error converting the column to an int occurred. Leading me to conclude that the only value that can be returned is an integer.

Essentially, I would think stored procedures are similar to a void function, not really intended to return a value. For the most part a UDF would be a better choice; however, in the instant case I need to call a stored procedure from within thereby ruling out a function.

So after all that, my question is: can a stored procedure return a type other than an integer? Specifically, can it return a decinal or money type?

Thanks,

Greg"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-18 : 09:46:44

from the Book OnLine aka BOL

quote:
RETURN
Exits unconditionally from a query or procedure. RETURN is immediate and complete and can be used at any point to exit from a procedure, batch, or statement block. Statements following RETURN are not executed.

Syntax
RETURN [ integer_expression ]

for store procedure, to return a value to the calling store pro or application, use the OUTPUT

quote:
Syntax
CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]

What you want is it function ? a User Defined Function can return any type that you defined

quote:
Syntax
Scalar Functions

CREATE FUNCTION [ owner_name. ] function_name
( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] )

RETURNS scalar_return_data_type

[ WITH < function_option> [ [,] ...n] ]

[ AS ]

BEGIN
function_body
RETURN scalar_expression
END

[KH]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources