Can sp return string ? - SQL Server Forums

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

Can sp return string ?

Author

Topic

andrewcw
Posting Yak Master

133 Posts

Posted - 2012-02-22 : 19:39:24

What's online on search confuses me. Is it possible for something other than int to be returned ?

ALTER Procedure [dbo].[CustomerFromLineNumber]

@id int = 3659,
@OutValue varchar(5) OUTPUT
AS
BEGIN
SET @OutValue=(SELECT Customer FROM dbo.CustomerID WHERE (dbo.CustomerID.id = @id))

RETURN @OutValue
END

If I execute the proc I get
Line 10
Conversion failed when converting the varchar value 'FOO' to data type int.

And the result screen has this

DECLARE @return_value int, @OutValue varchar(5)

andrewcw

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-02-22 : 19:43:56

You don't use RETURN for this, you just use OUTPUT variable. And you've already got it, so we just need to remove your RETURN statement. All done!

ALTER Procedure [dbo].[CustomerFromLineNumber]

@id int = 3659,
@OutValue varchar(5) OUTPUT
AS
BEGIN
SET @OutValue=(SELECT Customer FROM dbo.CustomerID WHERE (dbo.CustomerID.id = @id))
END

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-22 : 19:45:31

There are two ways to return parameter data from a stored proc. One is via the output parameter and the other is via return value. You can return only integer/integer expressions via the return value. You can return pretty much anything via parameters. So you should change your stored proc to remove the "RETURN @outValue" statement and then call it like this:

DECLARE	@ide int,@OutValue varchar(5);
set @OutValue = '';
set @id = 3659;
Exec dbo.CustomerFromLineNumber @id, @OutValue OUTPUT;
select @OutValue;

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-02-22 : 19:47:22

And on that note, leave RETURN for success/failure and the likes. Use OUTPUT variable to get data back.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

andrewcw
Posting Yak Master

133 Posts

Posted - 2012-02-22 : 19:58:45

Thanks very much !!!

andrewcw

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-02-22 : 20:08:21

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-22 : 20:13:26

Water Closet..

And the MAIN reason not to use (and WHY M$ allows it in the first place is a mystery_) is that SQL Server can OVERRIDE any value you place in the Return

Don't ask me what sense this makes...

Must be an earlier hack from the SQL Server "developers"..ya know..the people who originally that made the games back in the 80's that made M$ a household name...

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources