Stored proc return type error insists on INT

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 2012 Forums

Transact-SQL (2012)

Stored proc return type error insists on INT

Author

Topic

markus27183
Starting Member

4 Posts

Posted - 2014-09-18 : 06:44:32

Hi

When I execute the SP that is created by:

CREATE PROCEDURE [dbo].testSP
AS
BEGIN
DECLARE @retval varchar(200);
SET @retVal = 'ok.';
RETURN @retVal;
END
GO

i.e. running
EXEC testSP

gives the error:

Msg 245, Level 16, State 1, Procedure testSP, Line 7
Conversion failed when converting the varchar value 'ok.' to data type int.

But I want the return type to be a varchar(200);

What am i doing wrong?

markus27183
Starting Member

4 Posts

Posted - 2014-09-18 : 06:49:50

actually the same error comes from this simplified example:

CREATE PROCEDURE [dbo].testSP
AS -- EXEC [testSP]
BEGIN
RETURN 'ok'
END
GO

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2014-09-18 : 07:03:10

Return value can not be anything other than integer.
You either have to use OUTPUT parameter or SELECT statement to return varchar value to the caller.

Harsh Athalye
http://www.letsgeek.net/

markus27183
Starting Member

4 Posts

Posted - 2014-09-18 : 07:49:26

The return type of an SP must be an integer (or nothing)

To get it to give you back rows, you have to use a SELECT

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources