SQL Server using SCOPE_IDENTITY

Question

I have 2 tables :

CREATE TABLE [dbo].[Accessoires1]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Date] [datetime] NOT NULL,
    [ClientID] [int] NOT NULL,
    [TotalPrice] [varchar](50) NOT NULL
)

CREATE TABLE [dbo].[Accessoires2]
(
    [Accessoire1_ID] [int],
    [Date] [datetime] NOT NULL,
    [ClientID] [int] NOT NULL,
    [TotalPrice] [varchar](50) NOT NULL
)

Then I have a stored procedure Proc1 that insert values into table Accessoire1, and I have Proc2 which adds data to accessoires2 - but it needs the a specific accessoire1_ID

In my C# code, I execute those procedures at the same exact time so I need the Accessoires1.ID to be inserted into accessoires2 - how can I manage to do that?

I'm not sure what your question is. But, in my opinion, the answer is: Don't use `SCOPE_IDENTITY()`. Instead, learn about the `OUTPUT` clause (https://msdn.microsoft.com/en-us/library/ms177564.aspx). — Gordon Linoff, Jan 07 '17 at 18:39
Get the Scope_Identity as a part of the sql command in an output parameter from the 1st stored proc. See also http://stackoverflow.com/a/42655/1260204 — Igor, Jan 07 '17 at 18:40
Meh, `scope_identity()` is easier than working with `OUTPUT` when dealing with single row inserts IMO. Trying to assign the `OUTPUT` Clause to a scalar variable would involve using an intermediate table variable. — Martin Smith, Jan 07 '17 at 18:53
I think he just means sproc1 does and insert then calls SET var = SCOPE_IDENTITY() where var is an output parm on the sproc. Then in C# just get the output parm and pass it into the next sproc call. Where is a table var involved? — Rabid Penguin, Jan 07 '17 at 19:05
What do you mean with: _i execute those procedures at the same exact time_ Usually in C# you write code for the first and then for the second procedure. In that case it is easy to get back the last inserted identity value. Please add the relevant C# code that executes the two procedures. — Steve, Jan 07 '17 at 19:06
@RabidPenguin A table variable would be involved if avoiding scope_identity and using output as the first comment advises. Except if the intention is to OUTPUT INTO the second table directly. But chances are the two tables will have an FK relationship and so this would be blocked. — Martin Smith, Jan 07 '17 at 19:07
OH! I thought by OUTPUT you meant output variable. I was unaware of the sql output command used like that. Learn something new every day :) — Rabid Penguin, Jan 07 '17 at 19:16
@RabidPenguin though as the OP is round tripping back to the client they could just use `OUTPUT` without `INTO` and then read the scalar value there. — Martin Smith, Jan 07 '17 at 19:20
I would argue that instead of using two stored procedures, it's safer to insert the data into both tables with a single stored procedure, also saving a round trip between c# and sql server. — Zohar Peled, Jan 08 '17 at 06:42

CodingYoshi · Answer 1 · 2017-01-07T19:29:08.200

In your first procedure do this:

CREATE PROCEDURE nameOfSp
    @col1            VARCHAR(20),
    @newId           INT    OUTPUT
AS
BEGIN
    SET NOCOUNT ON

    -- your code

    -- assign identity to variable
    SELECT @newId = SCOPE_IDENTITY()

    RETURN
END

Your c# code will need to call the first stored procedure and passing it an output parameter like this:

SqlParameter newId = new SqlParameter();
newId.ParameterName = "@newId";
newId.DbType = DbType.Int32; 
newId.Direction = ParameterDirection.Output;

After the first procedure is executed, your C# code can get the value of newId returned like this:

int outValue = (int) newId.Value;

Now you can pass it to your 2nd procedure.

SQL Server using SCOPE_IDENTITY

1 Answers1