Pages

Thursday, 11 April 2013

SQL Server IDENTITY Value and Its Use


Sometimes we need last identity value for a row inserted in a table. For Example you are creating a request table as

CREATE TABLE TBL_REQUEST
(
REQID INT IDENTITY(1,1) PRIMARY KEY,
REQNAME VARCHAR(100),
REQ_SUBJECT VARCHAR(200),
REQ_DESCRIPTION VARCHAR(2000),
REQ_DATE DATETIME
)

And you want to return a request no. for User for future refrence.In this scenario, you need  to return last inserted identity value to the user.
There are three ways to get last identity value
  •      Using @@IDENTITY: Its scope is global. It returns identity value with respect to last inserted row in any table on Connection.

INSERT INTO TBL_REQUEST (REQNAME,REQ_SUBJECT,REQ_DESCRIPTION,REQ_DATE )
VALUES ('Uma','Regrading File Issue','Kindly Issue File HHH/123','4/10/2013' )
SELECT  @@Identity
Output:
3
If another insert take place after you insert, It will returns identity of latest insert table.
Ex:
Query 1:
INSERT INTO TBL_REQUEST (REQNAME,REQ_SUBJECT,REQ_DESCRIPTION,REQ_DATE )
VALUES ('Uma','Regrading File Issue','Kindly Issue File HHH/123','4/10/2013' )
 Query 2:
INSERT INTO TBL_REQUEST_History (REQNAME,REQ_SUBJECT,REQ_DESCRIPTION,REQ_DATE )
VALUES ('Uma','Regrading File Issue','Kindly Issue File HHH/123','4/10/2013' )

If you are exceuting first query and then execute SELECT  @@Identity But in between any body exceute Query 2.then SELECT  @@Identity Returns identity of TBL_REQUEST_History.




  •     Using  SCOPE_IDENTITY():It returns latest identity value in same scope(as Stored Procedure,function,trigger or batch).

Suppose you create a procedure that also return identity by insert query executed with in stored procedure.
CREATE PROC USP_INSERT_REQ
(
@reqid int output,
@reqname varchar(200),
@reqsub varchar(200),
@desc varchar(2000)
)
as
BEGIN

INSERT INTO TBL_REQUEST(REQNAME,REQ_SUBJECT,REQ_DESCRIPTION,REQ_DATE)
VALUES(@reqname,@reqsub,@desc,GETDATE())
SET @reqid=SCOPE_IDENTITY()
      END
It returns inserted identity value by USP_INSERT_REQ stored procedure.You can use return request id as refrence no for Requester.
In C#,Use this code to show returns Identity to user on Label.
protected void Button1_Click(object sender, EventArgs e)
    {

cnn.Open();
SqlCommand cmd = new                       SqlCommand("usp_INSERT_REQ", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@reqname ", txtReq.Text);
cmd.Parameters.AddWithValue("@reqsub", txtSub.Text);
cmd.Parameters.AddWithValue("@desc", txtdesc.Text);
SqlParameter sp = new SqlParameter();
sp = new SqlParameter("@reqid", SqlDbType.Int);
sp.Direction = ParameterDirection.Output;
cmd.Parameters.Add(sp);
cmd.ExecuteReader();
Label1.Text = "Your refrence Number is " + cmd.Parameters["@kid"].Value.ToString() + ".Please use this for all communications regrading this request";

  }

  •      Using  IDENT_CURRENT(table_name):(For particular table)It returns last identity value for defined table.

Ex:
Query1:
 INSERT INTO TBL_REQUEST (REQNAME,REQ_SUBJECT,REQ_DESCRIPTION,REQ_DATE )
VALUES ('Uma','Regrading File Issue','Kindly Issue File HHH/123','4/10/2013' )

Query2: 
INSERT INTO TBL_REQUEST_History (REQNAME,REQ_SUBJECT,REQ_DESCRIPTION,REQ_DATE )
VALUES ('Uma','Regrading File Issue','Kindly Issue File HHH/123','4/10/2013' )

SELECT  @@Identity: return identity value inserted by Query 2.
select IDENT_CURRENT('TBL_REQUEST') :return identity value inserted by query one.



No comments:

Post a Comment

Please leave a comment for this post