Post reply

how to return the result in a stored procedure

  • 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..

  • August 20, 2011 at 2:35 am

    #1371018

    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

  • August 20, 2011 at 2:59 am

    #1371021

    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?

  • August 20, 2011 at 3:00 am

    #1371022

    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?

  • August 20, 2011 at 5:31 am

    #1371046

    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

  • August 20, 2011 at 9:58 pm

    #1371116

    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)
    Intro to Tally Tables and Functions

  • August 22, 2011 at 3:20 pm

    #1371714

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

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

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