Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Output Parameter in Stored PRocedure Expand / Collapse
Author
Message
Posted Wednesday, April 3, 2013 3:40 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 12:09 AM
Points: 1,916, Visits: 2,337
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/
Post #1438232
Posted Wednesday, April 3, 2013 8:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:59 PM
Points: 13,007, Visits: 12,426
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1438374
Posted Wednesday, April 3, 2013 8:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 7, 2014 2:48 AM
Points: 15, Visits: 203
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
Post #1438384
Posted Wednesday, April 3, 2013 8:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:59 PM
Points: 13,007, Visits: 12,426
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1438391
Posted Wednesday, April 3, 2013 10:21 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:58 PM
Points: 20,705, Visits: 32,356
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

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1438463
Posted Wednesday, April 3, 2013 11:20 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 12:09 AM
Points: 1,916, Visits: 2,337
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/
Post #1438502
Posted Wednesday, April 3, 2013 11:21 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 12:09 AM
Points: 1,916, Visits: 2,337
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/
Post #1438503
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse