Output Parameter in Stored PRocedure

  • Hi all, I have created a sp in which I want to extract last identity value after doing insertion....

    But its giving me error that @new_identity parameter is not supplied

    Create Procedure [dbo].[BS_StoreAllocation_AddSTNDetails]

    @StoreCode int = 1,

    @CourierName varchar(30) = 'BLUE Dart',

    @CourierNo int = 98765732,

    @new_identity int OUTPUT

    AS

    BEGIN

    SET NOCOUNT ON;

    DECLARE @STN varchar(20)

    INSERT INTO GV_STNDetails

    VALUES

    (

    REPLACE(STR(@StoreCode,4),' ','0'),

    @CourierName,

    @CourierNo,

    GETDATE(),

    CONVERT(VARCHAR,LEFT(@CourierName,3)) + '-' + CONVERT(VARCHAR,@CourierNo) + CONVERT(VARCHAR(10),GETDATE(),112)

    )

    SELECT @new_identity = SCOPE_IDENTITY()

    RETURN

    END

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • kapil_kk (4/3/2013)


    Hi all, I have created a sp in which I want to extract last identity value after doing insertion....

    But its giving me error that @new_identity parameter is not supplied

    Create Procedure [dbo].[BS_StoreAllocation_AddSTNDetails]

    @StoreCode int = 1,

    @CourierName varchar(30) = 'BLUE Dart',

    @CourierNo int = 98765732,

    @new_identity int OUTPUT

    AS

    BEGIN

    SET NOCOUNT ON;

    DECLARE @STN varchar(20)

    INSERT INTO GV_STNDetails

    VALUES

    (

    REPLACE(STR(@StoreCode,4),' ','0'),

    @CourierName,

    @CourierNo,

    GETDATE(),

    CONVERT(VARCHAR,LEFT(@CourierName,3)) + '-' + CONVERT(VARCHAR,@CourierNo) + CONVERT(VARCHAR(10),GETDATE(),112)

    )

    SELECT @new_identity = SCOPE_IDENTITY()

    RETURN

    END

    Your code looks to be ok. Does the table have an identity column? How are you calling this?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Hi,

    Use Select max(identity column name) from table name (data inserted table). In one of our scenario we used this it worked. Try this.

    Vasu

  • devaji123 (4/3/2013)


    Hi,

    Use Select max(identity column name) from table name (data inserted table). In one of our scenario we used this it worked. Try this.

    Vasu

    This is not a good approach. You introduce concurrency issues like this. What happens when two or more connections are running the same code at the same time? Does one of them get the max value which now includes the insert from the second instance of it running? Using SCOPE_IDENTITY() is a better approach. Using OUTPUT is another option.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • kapil_kk (4/3/2013)


    Hi all, I have created a sp in which I want to extract last identity value after doing insertion....

    But its giving me error that @new_identity parameter is not supplied

    Create Procedure [dbo].[BS_StoreAllocation_AddSTNDetails]

    @StoreCode int = 1,

    @CourierName varchar(30) = 'BLUE Dart',

    @CourierNo int = 98765732,

    @new_identity int OUTPUT

    AS

    BEGIN

    SET NOCOUNT ON;

    DECLARE @STN varchar(20)

    INSERT INTO GV_STNDetails

    VALUES

    (

    REPLACE(STR(@StoreCode,4),' ','0'),

    @CourierName,

    @CourierNo,

    GETDATE(),

    CONVERT(VARCHAR,LEFT(@CourierName,3)) + '-' + CONVERT(VARCHAR,@CourierNo) + CONVERT(VARCHAR(10),GETDATE(),112)

    )

    SELECT @new_identity = SCOPE_IDENTITY()

    RETURN

    END

    Is this how you are invoking your procedure?

    declare @NewValue int; -- will have the value returned by @new_identity

    exec dbo.BS_StoreAllocation_AddSTNDetails

    @StoreCode = <someinput>,

    @CourierName = <someinput>,

    @CourierNo = <someinput>,

    @new_identity = @NewValue OUTPUT;

  • Lynn Pettis (4/3/2013)


    kapil_kk (4/3/2013)


    Hi all, I have created a sp in which I want to extract last identity value after doing insertion....

    But its giving me error that @new_identity parameter is not supplied

    Create Procedure [dbo].[BS_StoreAllocation_AddSTNDetails]

    @StoreCode int = 1,

    @CourierName varchar(30) = 'BLUE Dart',

    @CourierNo int = 98765732,

    @new_identity int OUTPUT

    AS

    BEGIN

    SET NOCOUNT ON;

    DECLARE @STN varchar(20)

    INSERT INTO GV_STNDetails

    VALUES

    (

    REPLACE(STR(@StoreCode,4),' ','0'),

    @CourierName,

    @CourierNo,

    GETDATE(),

    CONVERT(VARCHAR,LEFT(@CourierName,3)) + '-' + CONVERT(VARCHAR,@CourierNo) + CONVERT(VARCHAR(10),GETDATE(),112)

    )

    SELECT @new_identity = SCOPE_IDENTITY()

    RETURN

    END

    Is this how you are invoking your procedure?

    declare @NewValue int; -- will have the value returned by @new_identity

    exec dbo.BS_StoreAllocation_AddSTNDetails

    @StoreCode = <someinput>,

    @CourierName = <someinput>,

    @CourierNo = <someinput>,

    @new_identity = @NewValue OUTPUT;

    Thanks, Lyan

    I was invoking the procedure in the wrong way

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • devaji123 (4/3/2013)


    Hi,

    Use Select max(identity column name) from table name (data inserted table). In one of our scenario we used this it worked. Try this.

    Vasu

    When we have inbuilt function that replaces your query then why we used that.....

    Sean is correct about this....

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

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

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