April 3, 2013 at 3:40 am
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/
April 3, 2013 at 8:22 am
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/
April 3, 2013 at 8:32 am
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
April 3, 2013 at 8:42 am
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/
April 3, 2013 at 10:21 am
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;
April 3, 2013 at 11:20 am
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/
April 3, 2013 at 11:21 am
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