SQL Server 2012 scope_identity advise

Question

I created a stored procedure in SQL Server 2012 and I have used scope_identity to get identity column's value but in my case I do not know is this correct or not please help

CREATE PROCEDURE Add_Translation
    @english nvarchar(70),
    @kurdish nvarchar(70),
    @english_category int,
    @kurdish_category int,
    @result int out
AS
BEGIN
    SET NOCOUNT ON;

    if @english is not null and @kurdish is not null and @english_category is not null and @kurdish_category is not null
    begin
        insert into english_table values(@english, @english_category);

        declare @identityEnglish int;
        set @identityEnglish = SCOPE_IDENTITY();

        insert into kurdish_table values(@kurdish, @kurdish_category);
        declare @identityKurdish int;
        set @identityKurdish = SCOPE_IDENTITY();

        insert into transactions values(@identityEnglish, @identityKurdish);
        set @result = 1;
    end
    else
    begin
        set @result = 0;
    end
END

My question is that will the variable @identityEnglish get last identity value of english_table and variable @identityKurdish get last identity value of kurdish_table

thank you..

thank you for helping it is working but asked because both table identity column start with 1 and increment by 1 and I thought that scope_identity will remain same for the first transaction — danarj, Aug 29 '12 at 23:15

score 2 · Accepted Answer · edited Aug 30 '12 at 07:43

2

If the inserts succeed then scope_identity() works correctly.

edited Aug 30 '12 at 07:43

Andriy M

76,112
17
94
154

answered Aug 30 '12 at 00:15

Jimbo

2,529
19
22

score 0 · Answer 2 · answered Aug 30 '12 at 11:18

0

You can use "Output" clause on DML operations.

Click here

answered Aug 30 '12 at 11:18

sganesh

79
6

SQL Server 2012 scope_identity advise

2 Answers2