Sqlserver函数之SCOPE_IDENTITY()demo

　　之前有看到有人给我留言说希望有个SCOPE_IDENTITY()函数的demo,今天得闲，就整了个出来，希望对大家有帮助.既然做了就从头开始吧，先创建一个表，如果下

create table Tab1
(
id int identity primary key ,
Texts nvarchar ( 50 )
)

表结构很简单，就两个字段，一个ID，一个texts,ID是个整型自增长的主键，texts是个长度为50个字符的字符类型。表建好后，写存储过程。先贴代码再解释。

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Fuyuanzhao
-- Create date: 2011-3-25
-- Description: 添加测试数据
-- =============================================
CREATE PROCEDURE AddTexts
-- Add the parameters for the stored procedure here
@Texts nvarchar ( 50 ), @id int output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT Off ; -- 这里设置为OFF，这样会返回影响的行数，如果设置为ON这不会返回影响的行数

-- Insert statements for procedure here
insert into tab1(texts) values ( @Texts )
set @id = SCOPE_IDENTITY ()
END
GO

这是个往表tab1里插入数据的存储过程，需要两个参数。这存储过程很简单，就不再解释了，如有需要解释的请留言。存储过程写完后就是程序中怎么来调用存储过程，代码如下：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
namespace TestWeb
{
public partial class AddTextsProcedure : System.Web.UI.Page
{
protected void Page_Load( object sender, EventArgs e)
{
SqlConnection SqlConnection = new SqlConnection( " SqlConnectionString " );
try {
using (SqlCommand SqlCommand = new SqlCommand( " AddTexts " , SqlConnection))
{
SqlCommand.CommandType = CommandType.StoredProcedure;
SqlCommand.Parameters.Add( " @Texts " ,SqlDbType.NVarChar, 50 ).Value = " 张三 " ;
SqlCommand.Parameters.Add( " @id " , SqlDbType.Int, 8 ).Value = 0 ;
SqlCommand.Parameters[ " @id " ].Direction = ParameterDirection.Output;
if (SqlCommand.ExecuteNonQuery() == 1 )
{
int ID = ( int )SqlCommand.Parameters[ " @id " ].Value;
}

}
}
catch (Exception ex)
{
#if DEBUG
throw ex;
#else
#endif
}
finally
{
SqlConnection.Close();
}
}
}
}

这只是个简单的demo,我们往表tab1里插入了“张三"，我们的到的是张三的ID

转载于:https://www.cnblogs.com/_fyz/archive/2011/03/25/sql.html