November 25, 2008 at 5:06 am
I have an sproc I created in order that I could enter details into 2 tables but for it to enter the details into the 2nd table, it needs to obtain the identity value from the first insert. However, it is not getting the identity value - I'm not too familiar with SCOPE_IDENTITY so this is probably where I'm going wrong. My sproc is as follows and I'd be very grateful if someone could tell me where I'm going wrong:
ALTER PROCEDURE dbo.InsertCustomerDelivery
(
@Surname varchar(70),
@CompanyName varchar(70),
@Address1 varchar(70),
@Address2 varchar(70),
@Address3 varchar(70),
@TownCity varchar(50),
@PostCode varchar(12),
@StateRegion varchar(100),
@Country int,
@Tel varchar(50),
@Fax varchar(50),
@Email varchar(100),
@Userid varchar(20),
@Password varchar(20),
@OrderComments text,
@Forename varchar(100),
@Salutation varchar(15),
@AcceptTerms int,
@ContactName varchar(70),
@Comments text,
@CustomerId int OUTPUT
)
AS
Declare @NEWID int
INSERT INTO CS_Customers
(Surname,Company_Name,Address1,Address2,Address3,Town_City,Post_Code,State_Region,Country,Tel,Fax,Userid,Password,
Order_Comments,Forename,Salutation,Accept_Terms)
VALUES
(@Surname,@CompanyName,@Address1,@Address2,@Address3,@TownCity,@PostCode,@StateRegion,@Country,@Tel,@Fax,@Userid,
@Password,@OrderComments,@Forename,@Salutation,@AcceptTerms)
SELECT @CustomerId = SCOPE_IDENTITY()
Set @NEWID = @CustomerId
INSERT INTO CS_Delivery_Addresses
(Customer_ID,Contact_Name,Company_Name,Address1,Address2,Address3,Town_City,Post_Code,State_Region,Country,Tel,Comments)
VALUES
(@NEWID,@ContactName,@CompanyName,@Address1,@Address2,@Address3,@TownCity,@PostCode,@StateRegion,@Country,@Tel,
@Comments)
Thanks
Lorna
November 25, 2008 at 5:32 am
Looks like it should work to me - but can you post the full ddl of both tables - are you sure there is an identiity column on the first one?
Mike John
November 25, 2008 at 5:33 am
Take a look at the Books Online for the topic OUTPUT clause. That's exactly what you need. It can work with a single row insert, or with batches, and captures the IDENTITY values nicely. Read up on it. If you then have questions, post again.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 25, 2008 at 6:42 am
SCOPE_IDENTITY should work, but Grant is right in suggesting the output clause. I wonder why you are setting @customerid and then setting @NEWID to @customerid instead of just using @customerid later on.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 25, 2008 at 8:13 am
Checked and I definitely have the first field (CustomerId set as an identity column). I don't actually need the CustomerId in the application, I just need it passed to the second table in the sproc. I have also tried changing the sproc to SET @CustomerId = @@identity but that doesn't work either. Another post suggested I try Books Online but it looks like I need a full version of SQL Server to get access to this but I am using SQL Server Express!
November 25, 2008 at 8:29 am
BOL is also available on the MS web site and, I believe, is downloadable on it's own. The OUTPUT clause goes after your INSERT. Like this:
[font="Courier New"]DECLARE @table(CustomerId INT)
INSERT INTO CS_Customers
(
Surname,
Company_Name,
Address1,
Address2,
Address3,
Town_City,
Post_Code,
State_Region,
Country,
Tel,
Fax,
Userid,
Password,
Order_Comments,
Forename,
Salutation,
Accept_Terms
)
Output
I.CustomerId
INTO
@table
VALUES
(
@Surname,
@CompanyName,
@Address1,
@Address2,
@Address3,
@TownCity,
@PostCode,
@StateRegion,
@Country,
@Tel,
@Fax,
@Userid,
@Password,
@OrderComments,
@Forename,
@Salutation,
@AcceptTerms
)
SELECT
*
FROM
@table
[/font]
The you can use the table variable either in a select or to set your variable.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 25, 2008 at 8:30 am
Books Online are available on line here:
http://msdn.microsoft.com/en-us/library/ms130214.aspx
The OUTPUT clause that you want is here (this in the 2008 version, but it's the same):
http://msdn.microsoft.com/en-us/library/ms177564.aspx
Here is an article describing the OUTPUT clause:
http://blogs.msdn.com/sqltips/archive/2005/06/13/OUTPUT_clause.aspx
@@IDENTITY is not what you want. You should use SCOPE_IDENTITY() if you really want to do this type of work, but better still would be to look into the OUTPUT clause.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 25, 2008 at 10:37 am
Hey Grant, any idea on performance impact of Scope_Identity vs. the Output clause? I would imagine that they are similar, but doesn't hurt to ask.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 25, 2008 at 10:42 am
I did a simple test and the execution plans are the same with no apparent reference to the function or Output.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 25, 2008 at 10:48 am
For a single value from OUTPUT, I'm pretty sure it's free.
Where you might see a performance hit (or benefit, depending on how you define it) is when you define a temporar table (or table variable) and load a bunch of data from the OUTPUT in batch situations. Then the loading of the temp table will be an added cost. However, it's probably cheaper to load the temp table and then reference it later in the query rather than try to retrieve the data you just inserted into the real table. Especially if you needed to use it more than once in the rest of the query. Then you're either querying your base table over & over (not good) or you have to load a temporary table anyway but have to do it through a query. Pretty much, I'm working off the assumption that in most cases, OUTPUT is the way to go.
I don't have hard metrics on it though.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 25, 2008 at 2:57 pm
Thanks - you have all been really helpful and I have learnt some about OUTPUT into the bargain. I see the benefits of using OUTPUT in certain scenarios, however, to retrieve just one var Identity and insert it into another table, is it not ok just to do the following:
ALTER PROCEDURE dbo.InsertCustomerDelivery
(
@Surname varchar(70),
@CompanyName varchar(70),
@Address1 varchar(70),
@Address2 varchar(70),
@Address3 varchar(70),
@TownCity varchar(50),
@PostCode varchar(12),
@StateRegion varchar(100),
@Country int,
@Tel varchar(50),
@Fax varchar(50),
@Email varchar(100),
@Userid varchar(20),
@Password varchar(20),
@OrderComments text,
@Forename varchar(100),
@Salutation varchar(15),
@AcceptTerms int,
@ContactName varchar(70),
@Comments text
)
AS
DECLARE @CustomerId int
INSERT INTO CS_Customers
(Surname,Company_Name,Address1,Address2,Address3,Town_City,Post_Code,State_Region,Country,Tel,
Fax,Email,Userid,Password,Order_Comments,Forename,Salutation,Accept_Terms)
VALUES
(@Surname,@CompanyName,@Address1,@Address2,@Address3,@TownCity,@PostCode,@StateRegion,@Country,@Tel,
@Fax,@Email,@Userid,@Password,@OrderComments,@Forename,@Salutation,@AcceptTerms)
SELECT @CustomerId = IDENT_CURRENT('CS_Customers')
INSERT INTO CS_Delivery_Addresses
(Customer_ID,Contact_Name,Company_Name,Address1,Address2,Address3,Town_City,Post_Code,State_Region,Country,Tel,Comments)
VALUES
(@CustomerId,@ContactName,@CompanyName,@Address1,@Address2,@Address3,@TownCity,@PostCode,@StateRegion,@Country,@Tel,
@Comments)
This does exactly what I wanted and seems much simpler but I'm willing to listen to any advice as to why I shouldn't be doing it this way.
Thanks
Lorna
November 25, 2008 at 3:06 pm
You have to be careful with ident_current, since it
Returns the last identity value generated for a specified table or
view. The last identity value generated can be for any session and any scope.
(from Books Online)
That means that if two sessions are hitting the table at the same time, you could get the same identity returned to BOTH sessions, even though only one of the sessions created that ID. That's what the "can be for any session and any scope" part of that is about.
On the output clause however, you are guaranteed that the identity value or values you get back were generated on this table and by this session. Unless I'm badly mistaken, none of the other three options actually guarantees that.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 26, 2008 at 5:23 am
Matt's nailed it. If you went for the identity value on the table, you could get someone else's insert value. OUTPUT pretty much guarantees what you're getting. Still, in most cases, you could use SCOPE_IDENTITY. It only gets wonky when there are triggers involved. For a single value, that would work fine. I just prefer the direct control and certainty of the OUTPUT method.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 26, 2008 at 7:24 am
Grant Fritchey (11/26/2008)
Matt's nailed it. If you went for the identity value on the table, you could get someone else's insert value. OUTPUT pretty much guarantees what you're getting. Still, in most cases, you could use SCOPE_IDENTITY. It only gets wonky when there are triggers involved. For a single value, that would work fine. I just prefer the direct control and certainty of the OUTPUT method.
I'm not sure I agree if all you are after is the Identity value. According to BOL:
SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.
For example, there are two tables, T1 and T2, and an INSERT trigger is defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 by the trigger.
Assuming that both T1 and T2 have identity columns, @@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1. @@IDENTITY will return the last identity column value inserted across any scope in the current session. This is the value inserted in T2. SCOPE_IDENTITY() will return the IDENTITY value inserted in T1. This was the last insert that occurred in the same scope. The SCOPE_IDENTITY() function will return the null value if the function is invoked before any INSERT statements into an identity column occur in the scope.
SCOPE_IDENTITY has always been rock solid for me. Now I have been burned by @@IDENTITY but that was in SQL 7 BEFORE SCOPE_IDENTITY() was available.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 26, 2008 at 7:44 am
Sorry, you're right. It's leakage from the old days. SCOPE_IDENTITY specifically deals well with triggers. My bad.
I still like the OUTPUT approach becuase it works the same with one row or one thousand and I'm more frequently doing the latter rather than the former.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply