get id after inserting a record in sqlserver

Question

get id after inserting a record in sqlserver

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

How can I get the id of a record after it is inserted into a SQL table, I'm working with .net and sql server, if I have this stored procedure example

    CREATE PROCEDURE dbo.sp_Students_INS_byPK
       @student_id                     INT                      , 
       @password                       VARCHAR(15)      = NULL  , 
       @active_flg                     TINYINT                  , 
       @lastname                       VARCHAR(30)      = NULL  , 
       @birth_dttm                     DATETIME         = NULL  , 
       @gpa                            INT              = NULL  , 
       @is_on_staff                    TINYINT                   
AS 
BEGIN 
     SET NOCOUNT ON 

     INSERT INTO dbo.Students
          ( 
            student_id                   ,
            password                     ,
            active_flg                   ,
            lastname                     ,
            birth_dttm                   ,
            gpa                          ,
            is_on_staff                  
          ) 
     VALUES 
          ( 
            @student_id                   ,
            @password                     ,
            @active_flg                   ,
            @lastname                     ,
            @birth_dttm                   ,
            @gpa                          ,
            @is_on_staff                  
          ) 

END 

GO

sql sql-server

asked by Ivxn 12.03.2018 в 21:35

source

2 answers

Create a record if it does not exist in the table or update data if the record exists Destroy the previous function by clicking on another button

score 1 · Answer 1

There are different ways to do this, one of them would be to use SCOPE_IDENTITY You should only do SELECT after your INSERT

Ejm:
INSERT INTO dbo.Students(...) VALUES(...); SELECT SCOPE_IDENTITY();

You can also use @@IDENTITY

Ejm:
INSERT INTO dbo.Students(...) VALUES(...); SELECT @@IDENTITY AS 'Identity';

An alternative could be SCOPE_IDENTITY()

Ejm:
INSERT INTO dbo.Students(...) VALUES(...); SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];

score 0 · Answer 2

Based on this comment: I need to use that ID in my handler Maybe it is not the best method but you will get the desired id.

using(SqlConnection cn = new SqlConnection("connection string"))
{
    cn.Open();
    string comando = "select max(student_id) as id from Students";     
    SqlCommand cmd= new SqlCommand(comando, cn);
    SqlDataReader dr = cmd.ExecuteReader();

    if(dr.Read())
    {
        Console.WriteLine(Convert.ToString(reader("id")));
    }
}

You can even choose not to use a SqlDataReader at your option.

This taking into account that the field student_id is of type Identity and that you are only executing a Insert since you will always get the last record inserted, if you make a Update it would not work since it will bring again the last record in the table.