之前有看到有人给我留言说希望有个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
相关资源：数据结构—成绩单生成器