创建存储过程，存储过程是保存起来的可以接受和返回用户提供的参数的 Transact-SQL 语句的集合。
语法
CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ] [ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] [ FOR REPLICATION ] AS sql_statement [ ...n ]
参数
procedure_name新存储过程的名称。过程名必须符合标识符规则，且对于数据库及其所有者必须唯一。有关更多信息，请参见使用标识符。要创建局部临时过程，可以在 procedure_name 前面加一个编号符 (#procedure_name)，要创建全局临时过程，可以在 procedure_name 前面加两个编号符 (##procedure_name)。完整的名称（包括 # 或 ##）不能超过 128 个字符。指定过程所有者的名称是可选的。;number是可选的整数，用来对同名的过程分组，以便用一条 DROP PROCEDURE 语句即可将同组的过程一起除去。例如，名为 orders 的应用程序使用的过程可以命名为 orderproc;1、orderproc;2 等。DROP PROCEDURE orderproc 语句将除去整个组。如果名称中包含定界标识符，则数字不应包含在标识符中，只应在 procedure_name 前后使用适当的定界符。@parameter过程中的参数。在 CREATE PROCEDURE 语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值）。存储过程最多可以有 2.100 个参数。使用 @ 符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个过程的参数仅用于该过程本身；相同的参数名称可以用在其它过程中。默认情况下，参数只能代替常量，而不能用于代替表名、列名或其它数据库对象的名称。有关更多信息，请参见 EXECUTE。 data_type参数的数据类型。所有数据类型（包括 text、ntext 和 image）均可以用作存储过程的参数。不过，cursor 数据类型只能用于 OUTPUT 参数。如果指定的数据类型为 cursor，也必须同时指定 VARYING 和 OUTPUT 关键字。有关 SQL Server 提供的数据类型及其语法的更多信息，请参见数据类型。 说明 对于可以是 cursor 数据类型的输出参数，没有最大数目的限制。
VARYING指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化）。仅适用于游标参数。default参数的默认值。如果定义了默认值，不必指定该参数的值即可执行过程。默认值必须是常量或 NULL。如果过程将对该参数使用 LIKE 关键字，那么默认值中可以包含通配符（%、_、[] 和 [^]）。OUTPUT表明参数是返回参数。该选项的值可以返回给 EXEC[UTE]。使用 OUTPUT 参数可将信息返回给调用过程。Text、ntext 和 image 参数可用作 OUTPUT 参数。使用 OUTPUT 关键字的输出参数可以是游标占位符。n表示最多可以指定 2.100 个参数的占位符。{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}RECOMPILE 表明 SQL Server 不会缓存该过程的计划，该过程将在运行时重新编译。在使用非典型值或临时值而不希望覆盖缓存在内存中的执行计划时，请使用 RECOMPILE 选项。ENCRYPTION 表示 SQL Server 加密 syscomments 表中包含 CREATE PROCEDURE 语句文本的条目。使用 ENCRYPTION 可防止将过程作为 SQL Server 复制的一部分发布。说明 在升级过程中，SQL Server 利用存储在 syscomments 中的加密注释来重新创建加密过程。
FOR REPLICATION指定不能在订阅服务器上执行为复制创建的存储过程。.使用 FOR REPLICATION 选项创建的存储过程可用作存储过程筛选，且只能在复制过程中执行。本选项不能和 WITH RECOMPILE 选项一起使用。AS指定过程要执行的操作。sql_statement过程中要包含的任意数目和类型的 Transact-SQL 语句。但有一些限制。n是表示此过程可以包含多条 Transact-SQL 语句的占位符。
语法
CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ] [ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] [ FOR REPLICATION ] AS sql_statement [ ...n ]
参数
procedure_name新存储过程的名称。过程名必须符合标识符规则，且对于数据库及其所有者必须唯一。有关更多信息，请参见使用标识符。要创建局部临时过程，可以在 procedure_name 前面加一个编号符 (#procedure_name)，要创建全局临时过程，可以在 procedure_name 前面加两个编号符 (##procedure_name)。完整的名称（包括 # 或 ##）不能超过 128 个字符。指定过程所有者的名称是可选的。;number是可选的整数，用来对同名的过程分组，以便用一条 DROP PROCEDURE 语句即可将同组的过程一起除去。例如，名为 orders 的应用程序使用的过程可以命名为 orderproc;1、orderproc;2 等。DROP PROCEDURE orderproc 语句将除去整个组。如果名称中包含定界标识符，则数字不应包含在标识符中，只应在 procedure_name 前后使用适当的定界符。@parameter过程中的参数。在 CREATE PROCEDURE 语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值）。存储过程最多可以有 2.100 个参数。使用 @ 符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个过程的参数仅用于该过程本身；相同的参数名称可以用在其它过程中。默认情况下，参数只能代替常量，而不能用于代替表名、列名或其它数据库对象的名称。有关更多信息，请参见 EXECUTE。 data_type参数的数据类型。所有数据类型（包括 text、ntext 和 image）均可以用作存储过程的参数。不过，cursor 数据类型只能用于 OUTPUT 参数。如果指定的数据类型为 cursor，也必须同时指定 VARYING 和 OUTPUT 关键字。有关 SQL Server 提供的数据类型及其语法的更多信息，请参见数据类型。 说明 对于可以是 cursor 数据类型的输出参数，没有最大数目的限制。
VARYING指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化）。仅适用于游标参数。default参数的默认值。如果定义了默认值，不必指定该参数的值即可执行过程。默认值必须是常量或 NULL。如果过程将对该参数使用 LIKE 关键字，那么默认值中可以包含通配符（%、_、[] 和 [^]）。OUTPUT表明参数是返回参数。该选项的值可以返回给 EXEC[UTE]。使用 OUTPUT 参数可将信息返回给调用过程。Text、ntext 和 image 参数可用作 OUTPUT 参数。使用 OUTPUT 关键字的输出参数可以是游标占位符。n表示最多可以指定 2.100 个参数的占位符。{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}RECOMPILE 表明 SQL Server 不会缓存该过程的计划，该过程将在运行时重新编译。在使用非典型值或临时值而不希望覆盖缓存在内存中的执行计划时，请使用 RECOMPILE 选项。ENCRYPTION 表示 SQL Server 加密 syscomments 表中包含 CREATE PROCEDURE 语句文本的条目。使用 ENCRYPTION 可防止将过程作为 SQL Server 复制的一部分发布。说明 在升级过程中，SQL Server 利用存储在 syscomments 中的加密注释来重新创建加密过程。
FOR REPLICATION指定不能在订阅服务器上执行为复制创建的存储过程。.使用 FOR REPLICATION 选项创建的存储过程可用作存储过程筛选，且只能在复制过程中执行。本选项不能和 WITH RECOMPILE 选项一起使用。AS指定过程要执行的操作。sql_statement过程中要包含的任意数目和类型的 Transact-SQL 语句。但有一些限制。n是表示此过程可以包含多条 Transact-SQL 语句的占位符。
<%@ Import Namespace="System.Data.SqlClient" %><html>
<script language="VB" runat="server"> Sub GetSales_Click(Sender As Object, E As EventArgs) Dim DS As DataSet
Dim MyConnection As SqlConnection
Dim MyCommand As SqlDataAdapter MyConnection = New SqlConnection("server=(local)\NetSDK;database=northwind;Trusted_Connection=yes")
MyCommand = New SqlDataAdapter("Employee Sales By Country", MyConnection) MyCommand.SelectCommand.CommandType = CommandType.StoredProcedure MyCommand.SelectCommand.Parameters.Add(New SqlParameter("@Beginning_Date", SqlDbType.DateTime))
MyCommand.SelectCommand.Parameters("@Beginning_Date").Value = BeginDate.SelectedDate MyCommand.SelectCommand.Parameters.Add(New SqlParameter("@Ending_Date", SqlDbType.DateTime))
MyCommand.SelectCommand.Parameters("@Ending_Date").Value = EndDate.SelectedDate DS = new DataSet()
MyCommand.Fill(DS, "销售额") MyDataGrid.DataSource=DS.Tables("销售额").DefaultView
MyDataGrid.DataBind()
End Sub</script><body> <form runat="server"> <h3><font face="宋体">对 DataGrid 控件的参数化存储过程选择</font></h3> <table width="700">
<tr>
<td valign="top" >
<b>开始日期</b>
<ASP:Calendar id="BeginDate"
BorderWidth="2"
BorderColor="lightblue"
Font-Size="8pt"
TitleStyle-Font-Size="8pt"
TitleStyle-BackColor="#cceecc"
DayHeaderStyle-BackColor="#ddffdd"
DayHeaderStyle-Font-Size="10pt"
WeekendDayStyle-BackColor="#ffffcc"
SelectedDate="7/1/1996"
VisibleDate="7/1/1996"
SelectedDayStyle-BackColor="lightblue"
runat="server"/>
</td>
<td valign="top" >
<b>结束日期</b>
<ASP:Calendar id="EndDate"
BorderWidth="2"
BorderColor="lightblue"
Font-Size="8pt"
TitleStyle-Font-Size="8pt"
TitleStyle-BackColor="#cceecc"
DayHeaderStyle-BackColor="#ddffdd"
DayHeaderStyle-Font-Size="10pt"
WeekendDayStyle-BackColor="#ffffcc"
SelectedDate="7/25/1996"
VisibleDate="7/25/1996"
SelectedDayStyle-BackColor="lightblue"
runat="server"/> </td>
<td valign="top" style="padding-top:20">
<input type="submit" OnServerClick="GetSales_Click" Value="获取员工销售额" runat="server"/><p>
</td>
</tr>
<tr>
<td colspan="3" style="padding-top:20">
<ASP:DataGrid id="MyDataGrid" runat="server"
Width="500"
BackColor="#ccccff"
BorderColor="black"
ShowFooter="false"
CellPadding=3
CellSpacing="0"
Font-Name="宋体"
Font-Size="8pt"
HeaderStyle-BackColor="#aaaadd"
EnableViewState="false"
/>
</td>
</tr>
</table> </form></body>
</html>
CREATE Procedure GetAuthors AS
SELECT * FROM Authors
return
GO可以创建同样接受参数的存储过程。例如：
CREATE Procedure LoadPersonalizationSettings (@UserId varchar(50)) AS
SELECT * FROM Personalization WHERE UserID=@UserId
return
GO从 ASP.NET 页使用存储过程只是您迄今为止已经学习的 SqlCommand 对象知识的扩展。CommandText 只是存储过程的名称，而不是特殊查询文本。通过设置 CommandType 属性，向 SqlCommand 指出 CommandText 是存储过程。
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
SET LastName = @LastName, FirstName = @FirstName, BirthDate = @BirthDate
WHERE (EmployeeID = @Emp_id)
当执行该语句时，必须为所有参数（@LastName、@FirstName、@BirthDate 和 @Emp_id）提供值。若要这样做，可使用参数对象。数据命令支持 Parameters 集合，它包含一组 OleDbParameter 类或 SqlParameter 类类型的对象。对于需要传递的每个参数，集合中均有一个对应的参数对象。此外，如果要调用存储过程，可能需要一个附加参数来接受过程的返回值。注意 如果正在使用数据集，通常不直接执行数据命令。而使用适配器特定的方法设置参数。有关更多信息，请参阅将参数用于 DataAdapter。
可以使用“属性”窗口或代码配置命令的 Parameters 集合。有关使用“属性”窗口的更多信息，请参阅为数据适配器配置参数。有关如何以编程方式创建和配置参数的示例，请参阅将存储过程用于命令。提示 最好为您的参数对象命名，以便可以方便地在代码中引用它们。尽管可以使用参数对象在 Parameters 集合中的索引值来引用它，但通过名称引用更清楚也更不易出错。
在执行命令以前，必须为命令中的每个参数设置值。设置参数值 对于命令的 Parameters 集合中的每个参数，将其 Value 属性设置为要传递的值。
下面的示例展示在执行引用存储过程的命令以前如何设置参数。本示例假定您已配置了具有 au_id、au_lname 和 au_fname 三个参数的 Parameters 集合。各个参数均通过名称设置，以清楚表明正在设置哪个参数。 ' Visual Basic
' The following two properties can be set in the Properties window
' but are shown here for completeness.
With OleDbCommand1
.CommandText = "UpdateAuthor"
.CommandType = System.Data.CommandType.StoredProcedure
.Parameters("au_id").Value = listAuthorID.Text
.Parameters("au_lname").Value = txtAuthorLName.Text
.Parameters("au_fname").Value = txtAuthorFName.Text
End With
OleDbConnection1.Open()
OleDbCommand1.ExecuteNonQuery()
OleDbConnection1.Close()// C#
// The following two properties can be set in the Properties window
// but are shown here for completeness.
OleDbCommand1.CommandText = "UpdateAuthor";
OleDbCommand1.CommandType = System.Data.CommandType.StoredProcedure;
OleDbCommand1.Parameters["au_id"].Value = listAuthorID.Text;
OleDbCommand1.Parameters["au_lname"].Value = txtAuthorLName.Text;
OleDbCommand1.Parameters["au_fname"].Value = txtAuthorFName.Text;
OleDbConnection1.Open();
OleDbCommand1.ExecuteNonQuery();
OleDbConnection1.Close();
获取返回值
存储过程常常将值传递回调用这些过程的应用程序。它们可以通过参数传递值或通过定义并传递返回值来完成该操作。获取过程返回的值 创建一些参数，将它们的 Direction 属性设置为 Output 或 InputOutput（如果该参数在过程中既用于接收值也用于发送值）。确保参数的数据类型与预期的返回值匹配。
执行过程后，读取所传递回参数的“值”(Value) 属性。
获取过程的返回值 创建一个参数，将其 Direction 属性设置为 ReturnValue。
注意 返回值的参数对象必须是 Parameters 集合中的第一项。
确保参数的数据类型与预期的返回值匹配。
注意 “更新”(Update)、“插入”(Insert) 和“删除”(Delete) SQL 语句返回一个整数值，指示受该语句影响的记录数。可以 ExecuteNonQuery 方法的返回值的形式获取该值。有关更多信息，请参阅使用数据命令执行更新或数据库命令。
下面的示例展示如何获取 CountAuthors 存储过程的返回值。在此情况下，假定该命令的 Parameters 集合中的第一个参数被命名为“retvalue”，用 ReturnValue 的一个方向配置。' Visual Basic
Dim cntAffectedRecords As Integer
' The following two property settings can also me done
' in the Properties window, but are shown here for completeness.
OleDbcommand1.CommandText = "CountAuthors"
OleDbCommand1.CommandType = CommandType.StoredProcedure
OleDbConnection1.Open()
OleDbCommand1.ExecuteNonQuery()
OleDbConnection1.Close()
cntAffectedRecords = CType(OleDbCommand1.Parameters("retvalue").Value, Integer)
MessageBox.Show("Affected records = " & cntAffectedRecords.ToString)// C#
int cntAffectedRecords;
oleDbcommand1.CommandText = "CountAuthors";
oleDbCommand1.CommandType = CommandType.StoredProcedure;
oleDbConnection1.Open();
oleDbCommand1.ExecuteNonQuery();
oleDbConnection1.Close();
cntAffectedRecords = (int)(OleDbCommand1.Parameters["retvalue"].Value);
MessageBox.Show("Affected records = " + cntAffectedRecords.ToString());