How to know the identity value to use in another stored procedure, Inserting from a windows form

Question

I need to use the Identity value of a an inserted record, to insert in another table. The insertions are done using stored procedures, which are called from a winforms button event. Procedures are parameterised, so i can't execute the first procedure in another procedure

Procedure [dbo].[insert_trans] (
  @name nvarchar(50),
  @email nvarchar(50),
  @ata_certification bit,
  @St1 nvarchar(50),
  @St2 nvarchar(50),
  @City nvarchar(MAX),
  @State nvarchar(MAX),
  @Country nvarchar(MAX),
  @Zipcode numeric(18, 0),
  @Qualification nvarchar(50),
  @Interpreting_service bit ,
  @Tran_Int_Degree bit,
  @Total_Experience int,
  @Native_language nvarchar(50),
  @Resume bit
) as
begin
    insert into Location_Master (
        St1,
        St2, 
        City, 
        State, 
        Country, 
        Zipcode
    ) values (
        @St1,
        @St2,
        @City,
        @State,
        @Country,
        @Zipcode)

    Declare @Location_id int
    set @Location_id = (SELECT CAST(SCOPE_IDENTITY() AS varchar(10))
                        AS LAST_IDENTITY)

    insert into Translators values (
        @name, 
        @email,
        @ata_certification,
        @Location_id,             
        @Qualification, 
        @Interpreting_service,
        @Tran_Int_Degree,             
        @Total_Experience, 
        @Native_language, 
        @Resume)

end

and the second procedure, where I require the sno generated

procedure insert_langknown (
    @translator_id int,
    @Language_code varchar(50)
) as
begin

    Insert into Language_known (
        translator_id,
        Language_code
    ) values (
        @translator_id,
        @Language_code)
end

The translator id is the required field

Make the first procedure return the value of the newly inserted identity column. You can retrieve the new identity value with [`scope_identity`()](https://msdn.microsoft.com/en-us/library/ms190315.aspx) — Andomar, Jul 07 '15 at 15:09
Preferably both. I want to see what classes you are using to call the procs and what the procs do. — Michael, Jul 07 '15 at 15:13
@Andomar How do i use scope_identity() returned value in another procedure? — A__, Jul 07 '15 at 15:14
Possible duplicate: http://stackoverflow.com/questions/5228780/how-to-get-last-inserted-id Note that scope_identity() is no longer required for newer version of SQL. — Michael, Jul 07 '15 at 15:16
@Andomar `OUTPUT INSERTED.ID` or whatever the name of the Identity column happens to be. — Michael, Jul 07 '15 at 15:43

score 0 · Answer 1 · answered Jul 07 '15 at 15:26

You can return value from store procedure as below

Select Scope_Identity()

and can access the return value in vb.net code as below

command.ExecuteScalar()

otherwise you directly send the inserted value record to another procedure simply as

DECLARE @RETURNVAL INT
SELECT @RETURNVAL=SCOPE_IDENTITY()

EXEC ANOTHER_PROCEDURE @RETURNVAL,another_parameter


-- 'ANOTHER_PROCEDURE' is Storepd procedure name

Anuj Tripathi · Accepted Answer · 2015-07-07T16:14:26.200

-2

You can get the recent inserted key via following way

 EXEC [Schema].[YourSpName] @Parameter ='ParamValue'
 SELECT  @@Identity

Scope_Identity() will return you NULL because connection scope will be lost.

Alternatively, you can use IDENT_CURRENT to be sure about the actual result as it is not dependent upon connection or scope.

  EXEC [Schema].[YourSpName] @Parameter ='ParamValue'
  SELECT IDENT_CURRENT('YourTableName')

Edit 1 As mentioned by Andomar, I will suggest you to return Scope_Identity from your stored procedure or use transaction to ensure that you are locking rows.

Edit 2 You can modify your SP to return OUTPUT value which you can use while calling other SP, like below

Stored procedure

    ALTER PROCEDURE insertSomething
(
 @Name varchar(20),
 @EffectIveRows INT OUTPUT
)
AS

INSERT INTO dbo.YourTable 
VALUES (@Name)
SELECT @EffectIveRows = SCOPE_IDENTITY()

Second SP

procedure insert_langknown
              (
                   @translator_id int,
                   @Language_code varchar(50)
                   @LastIdentityValue INT,

               )
               as
            begin
               -- USE @LastIdentityValue variable 
            Insert into Language_known (translator_id,Language_code) values ( @translator_id,@Language_code)

Calling your SP

DECLARE @Output INT = 0
EXEC insertSomething 'A' , @EffectIveRows = @Output OUTPUT

EXEC insert_langknown @translator_id= 2, @Language_code= 'dummy Data'  ,@LastIdentityValue = @Output

edited Jul 07 '15 at 16:14

answered Jul 07 '15 at 15:25

Anuj Tripathi

2,251
14
18

-1 This is not concurrent. If another connection inserts another row before you call `@@identity` or `ident_current()`, you'll get the wrong value. – Andomar Jul 07 '15 at 15:26
@Andomar Agree, I did forget that. I have edited my answer with suggestion. – Anuj Tripathi Jul 07 '15 at 15:36
Can u modify the second procedure i posted? – A__ Jul 07 '15 at 16:10
@newuser I have modified my answer with modified SP – Anuj Tripathi Jul 07 '15 at 16:15
i modified my sp1, and added the folllowing code to my form: SqlParameter retval = new SqlParameter("@EffectIveRows", SqlDbType.Int); retval.Direction = ParameterDirection.ReturnValue; Command.Parameters.Add(retval); but it gives the following error Procedure or function 'insert_trans' expects parameter '@EffectIveRows', which was not supplied. – A__ Jul 07 '15 at 17:08
You have to provide a default value to it. Just like in my example I have given 0 to it. I have very less c# knowledge but I remember that you can provide value to SqlParameter also – Anuj Tripathi Jul 07 '15 at 17:58
Yes it did, Thanks! :) – A__ Jul 08 '15 at 14:58

How to know the identity value to use in another stored procedure, Inserting from a windows form

2 Answers2