深入解析与精通：SQL Server存储过程技巧与实用指南

发布时间：2024-07-09 14:34:29 所属栏目：MsSql教程来源：DaWei

导读： 这篇文章主要介绍了如何掌握Sql Server存储过程，哪些知识要了解相关知识，内容详细易懂，操作简单快捷，具有一定借鉴价值，相信大家阅读完这篇如何掌握Sql Server存储过程，哪些知识要了解

这篇文章主要介绍了如何掌握Sql Server存储过程，哪些知识要了解相关知识，内容详细易懂，操作简单快捷，具有一定借鉴价值，相信大家阅读完这篇如何掌握Sql Server存储过程，哪些知识要了解文章都会有所收获，下面我们一起来看看吧。

存储过程它是真正的脚本，更准确地说，它是批处理(batch)，但都不是很确切，它存储与数据库而不是单独的文件中。

存储过程中有输入参数，输出参数以及返回值等。

一、创建存储过程:CREATE PROC

创建存储过程的方法除了他使用AS关键字外，和创建数据库中任何其他对象一样。存储过程的基本语法如下：

在语法中，PROC是PROCEDURE的缩写，两个选项的意思一样。在对存储过程命名完之后，接着是参数列表。参数是可选的。关键字AS其后就是实际的代码。

CREATE PROCEDURE|PROC <sproc name>
　　[ [schema.] <data type> [VARYING] [=] [OUT[PUT]] [READONLY]
　　[, [schema.] <data type> [VARYING] [=] [OUT[PUT]] [READONLY]
　　[,...
　　...
　　]]
　　[WITH
　　RECOMPILE | ENCRYPTION | [EXECUTE AS { CALLER | SELF | OWNER | <'user name'>}]
　　AS
　　<code> | EXTERNAL NAME <assembly name>.<assembly class>.<method>
简单的存储过程示例：

CREATE PROC spPerson
　　AS
SELECT * FROM Person
执行存储过程：

EXEC spPerson

1、声明参数
声明参数需要以下几部分的信息:名称、数据类型、默认值、方向、

对于名称，有一组简单的规则。

它必须以@符号(和变量一样)开始。除了不能内嵌空格外，其规则与普通变量规则相同。
数据类型和名称一样，必须像变量那样声明，采用SQL Server内置的或用户自定义的数据类型。
声明需要类型时需要注意，当声明CURSOR类型参数时，必须也使用VARYING和OUTPUT选项。同时，OUTPUT可以简写为OUT。

2、输出参数
一个获得OUTPUT参数的存储过程：

CREATE PROC InsertPerson
　　@Id int OUTPUT　　--必须注明为OUTPUT
　　AS
　　INSERT INTO Person　VALUES('刘备',22,190,'不详','未婚','幼儿园','不详',4999999)
　　SET @Id = @@IDENTITY
执行存储过程：

DECLARE @Id int　　--实际上，调用时名称可以不同，例如也可以为@Num,@i等等。
EXEC InsertPerson @Id OUTPUT --注意此处也要有OUTPUT
SELECT @Id

3、返回值。返回值必须是整数。
返回值可用来确定存储过程执行的状态。

SQL Server默认会在完成存储过程时自动返回一个0值。

为了从存储过程向调用代码传递返回值，只需要使用RETURN语句。

RETURN []
要特别注意的是：返回值必须是整数。

关于RETURN语句，最重要的是知道它是无条件地从存储过程中退出的。无论运行到存储过程的哪个位置，在调用RETURN语句之后将不会执行任何一行代码。

下面的存储过程，让其返回一个指定的值，以指示执行状态。

CREATE PROC spTestReturns
　　AS
　　DECLARE @MyMessage nvarchar(50);
　　DECLARE @MyOtherMessage nvarchar(50);

　　SELECT @MyMessage = '第一个RETURN';
　　PRINT @MyMessage;
　　RETURN 100; --将这里改成返回100

　　SELECT @MyOtherMessage = '第二个RETURN';
　　PRINT @MyOtherMessage;
　　RETURN;
执行之后，显示结果如下：

DECLARE @Return int
EXEC @Return = spTestReturns //第一个RETURN
SELECT @Return //返回100

4、执行存储过程：
对于调用存储过程需要注意以下几点：

对于存储过程声明中的输出参数，需要使用OUTPUT关键字。

和声明存储过程时一样，调用存储过程时，必须使用OUTPUT关键字。这样就对SQL Server作了提前通知，告诉它参数所需要的特殊处理。但需要注意的是，如果忘记包含OUTPUT关键字，不会产生运行时错误，但是输出的值不会传入变量中(变量很可能是NULL)。

赋值给输出结果的变量不需要和存储过程中的内部参数拥有相同的名称。

EXEC(或EXECUTE)关键字是必须的，因为对存储过程的调用并不是批处理要做的第一件事(如果存储过程的调用是批处理的第一件事，则可以不使用EXEC)。

5、WITH RECOMPILE选项
可以利用存储过程提供的安全性代码和代码封装方面的好处，但还是忽略了预编译代码方面的影响。可以回避未使用正确的查询计划的问题，因为可以确保为特定一次运行创建新的计划。方法就是使用WITH RECOMPILE选项。

使用该选项的方式有两种：

1、可以在运行时包含WITH RECOMPILE。这告诉SQL Server抛弃已有的执行计划并且创建一个新的计划-但只是这一次。也就是说，只是这次使用WITH RECOMPILE选项来执行存储过程。

EXEC spMySproc '1/1/2004'
　　WITH RECOMPILE
2、也可以通过在存储过程中包含WITH RECOMPILE选项来使之变得更持久。

如果使用这种方式，则在CREATE PROC或ALTER PROC语句中的AS语句前添加WITH RECOMPILE选项即可。如果通过该选项创建存储过程，那么无论在运行时选择了其他什么选项，每次运行存储过程都会重新编译它。

二、修改存储过程:ALTER PROC
ALTER PROC spPerson
　　AS
　　SELECT * FROM Person WHERE Id = 45

三、删除存储过程:DROP PROC
DROP PROC|PROCEDURE <sproc name>[;]

四、常用存储过程
1、sp_help：查询表的信息
sp_help Person
看一张表有那些信息，有约束，存储过程，自定义函数等等信息。

图文无关,原创配图

2、sp_helpdb：查看数据库信息
sp_helpdb TestDataCenter
当然也可以不带参数，显示当前数据库连接下的所有数据库信息。

3、sp_helpindex：查看有关表或视图上的索引的信息
sp_helpindex Person
注意参数中是表名，上面的Person就是表名，而不是索引名称。

4、sp_helpconstraint：查看表上的约束信息
sp_helpconstraint Person
注意参数是表名。

5、sp_helpfile：根据文件逻辑名称，查看文件的信息
sp_helpfile TestDataCenter
注意参数是文件的逻辑名称。也可以不带参数，输出当前数据库的所有文件信息。

6、sp_helpfilegroup：根据文件组名称，查看文件组信息
sp_helpfilegroup 'PRIMARY'
参数名中是文件组的逻辑名称，当然也可以不带参数，这样就仅仅输出当前数据库的文件组信息。

到此这篇关于“如何掌握Sql Server存储过程，哪些知识要了解”的文章就介绍到这了,更多相关如何掌握Sql Server存储过程，哪些知识要了解内容，小编将为大家输出更多高质量的实用文章！

（编辑：威海站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!