What is SCOPE_IDENTITY ? How to use SCOPE_IDENTITY?
Description :
SCOPE_IDENTITY() is defined as return last identity inserted in the identity column. Using this SCOPE_IDENTITY() you can use many inserted statement to insert same auto generated id value many table for as same reference.
In my previous article I have explained how to get inserted id from sql server that time we know how to get through query. In this article I have explained get auto generated ID using SCOPE_IDENTITY() from stored procedure.
Example1
In this example I have get Auto generated ID using SCOPE_IDENTITY() and OUTPUT variable from the stored procedure to display values in label controlCreate table like below
create table emp(eno int identity(1,1) NOT NULL,empname varchar(50),sal bigint)
Create stored procedure like below
--Procedure
create procedure Emp_return
@empname varchar(50),
@sal bigint,
@c1 int output
as
Begin
insert into emp(empname,sal) values(@empname,@sal)
set @c1 = SCOPE_IDENTITY()
return
End
Call from SQL SERVER
declare @t int
exec Emp_return2 @empname='Ravindran',@sal='45000',@c1=@t OUTPUT
select @t
Call from ASP.NET Code behind
Client side
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>How to get last inserted ID</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table width="600" cellpadding="0" cellspacing="0" align="center">
<tr>
<td height="40" colspan="2">
<b>Get Last Inserted ID</b>
</td>
</tr>
<tr>
<td height="40" colspan="2">
<b><asp:Label ID="lblResult" runat="server"></asp:Label></b>
</td>
</tr>
<tr>
<td height="30">
Enter Employee name
</td>
<td height="30">
<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td height="30">
Enter Employee salary
</td>
<td height="30">
<asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td height="40" align="center" colspan="2">
<asp:Button ID="btnInsert" runat="server" Text="Insert and Get ID"
onclick="btnInsert_Click" />
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
Server side
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
public partial class _Default : System.Web.UI.Page
{
SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["Con"].ToString());
SqlCommand sqlcmd = new SqlCommand();
protected void Page_Load(object sender, EventArgs e)
{
lblResult.Text = "";
}
protected void btnInsert_Click(object sender, EventArgs e)
{
string i;
sqlcon.Open();
sqlcmd = new SqlCommand("Emp_return", sqlcon);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.Add("@empname", SqlDbType.VarChar,50).Value = TextBox2.Text;
sqlcmd.Parameters.Add("@sal", SqlDbType.BigInt).Value = Convert.ToInt32(TextBox3.Text);
sqlcmd.Parameters.Add("@c1", SqlDbType.Int);
sqlcmd.Parameters["@c1"].Direction = ParameterDirection.Output;
sqlcmd.ExecuteNonQuery();
i = sqlcmd.Parameters["@c1"].Value.ToString(); //here i is get out parameter return value
lblResult.Text= "Last Insert Auto Generated ID is " + i.ToString();
sqlcon.Close();
TextBox2.Text = "";
TextBox3.Text = "";
}
}
Output:
Example2
In this example I have explained get that auto generated id from stored procedure and insert into another table with same reference number.Create table like below
create table student(s_id int identity(1,1) NOT NULL, sname varchar(50), sclass varchar(50))
create table fee(s_id int, sname varchar(50), s_fee varchar(50),s_month varchar(50))
Here after insert student detail I inserted that student fee details in another table with that same auto generated student id.
Create Stored Procedure
create procedure Student_Reg
@sname varchar(50),
@sclass varchar(50),
@smonth varchar(50),
@sfee varchar(50)
AS
Begin
declare @IDFee varchar(50)
insert into student(sname,sclass) values(@sname,@sclass)
insert into fee values(SCOPE_IDENTITY(),@sname,@sfee,@smonth)
End
Test in SQL SERVER
--Execute to test
exec Student_Reg @sname='ravindran',@sclass='MCA',@smonth='July',@sfee='25000'
If you execute the above stored procedure from code behind then same auto generated value get from student table and insert into fee table with the same reference number.
Source code:
Client Side: ASP.NETCode Behind: C#
Conclusion
I hope this article is help you to know about SCOPE_IDENTITY() use and know about to get last inserted auto generated id.
Attachments
No comments:
Post a Comment