how to return the result in a stored procedure

Question

Post reply

how to return the result in a stored procedure

Prasanthi Reddy

SSC Veteran

Points: 213
More actions
August 20, 2011 at 2:08 am

#390231

Hi,
I wrote a procedure as below...
Create PROCEDURE [dbo].[GET_HEIRARCHY_CODE] (@STR VARCHAR(50))
AS
BEGIN
DECLARE @HIER_NAME VARCHAR(30);
DECLARE @HIER_CODE VARCHAR(20);
SET @HIER_NAME = @STR;
SET @HIER_CODE = SUBSTRING(@HIER_NAME,CHARINDEX('[',@HIER_NAME)+1,CHARINDEX(']',@HIER_NAME)-(CHARINDEX('[',@HIER_NAME))-1);
END
i need to take the output @HIER_CODE .. when I say return @HIER_CODE i am getting the error as
'Conversion failed when converting the varchar value 'CRRN' to data type int.'
pls suggest how can i show the out put..
to execute this procedure give the string like abcd[xyz]
then i should get the output as xyz which is in the square brackets..
pls help..

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

VENUGOPAL.RR Ten Centuries Points: 1395 More actions · Answer 1

yes. you cannot return a string value using RETURN statement.

if you want it as out put can do it in two ways.

1. declare a out parameter and assign that value to that out parameter.

2. use select statement, you will get result in table.

Ex: select HOST_NAME = @HOST_NAME

Prasanthi Reddy SSC Veteran Points: 213 More actions · Answer 2

Thanks for the reply..

I tried as create procedure GET_HEIRARCHY_CODE(@str varchar(50),@code varchar(30) OUTPUT)

AS

BEGIN

-----statements

RETURN @CODE;

END

but while executing it is asking to pass value for @code also..

can u pls explain how to do this using out variable?

Prasanthi Reddy SSC Veteran Points: 213 More actions · Answer 3

Thanks for the reply..

I tried as create procedure GET_HEIRARCHY_CODE(@str varchar(50),@code varchar(30) OUTPUT)

AS

BEGIN

-----statements

RETURN @CODE;

END

but while executing it is asking to pass value for @code also..

can u pls explain how to do this using out variable?

k.srikalyan SSC-Addicted Points: 429 More actions · Answer 4

please go through this link.this may help you out.

http://stackoverflow.com/questions/1589466/sql-server-execute-stored-procedure-with-output-parameter

http://stackoverflow.com/questions/1248244/sql-server-output-parameter-issue

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

Prasanthi Reddy (8/20/2011)
Thanks for the reply..
I tried as create procedure GET_HEIRARCHY_CODE(@str varchar(50),@code varchar(30) OUTPUT)
AS
BEGIN
-----statements
RETURN @CODE;
END
but while executing it is asking to pass value for @code also..
can u pls explain how to do this using out variable?

You must also give @code a default if you don't intend to use it as an input. You've also been told previously that you cannot use RETURN with a VARCHAR variable.

Your procedure should be ...

create procedure GET_HEIRARCHY_CODE(@str varchar(50),@code = NULL varchar(30) OUTPUT)

AS

BEGIN

-----statements

SELECT @Code = whatever

RETURN;

END

Then the EXEC statment must pass the output variable to another variable. Take a look at EXECUTE in Books Online for more information on how to properly do this.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 6

Is there some reason it needs to be a procedure? Because this looks more like a function to me.