1.使用SQL Server管理平台创建存储过程
在SQL Server管理平台中,创建存储过程的步骤如下:
(1)打开SQL Server管理平台,展开结点“对象资源管理器”→“数据库服务器”→“可编程性”→“存储过程”,在窗口的右侧显示出当前数据库的所有存储过程。右击,在弹出的快捷菜单中选择“新建存储过程”命令。
(2)在打开的SQL命令窗口中,系统给出了创建存储过程命令的模板。在模板中输入创建存储过程的Transact-SQL语句后,单击“执行”按钮即可创建存储过程。
(3)建立存储过程的命令被成功执行后,选择“对象资源管理器”→“数据库服务器”→“可编程性”→“存储过程”命令,可以看到新建立的存储过程。
2.使用CREATE PROCEDURE语句创建存储过程
SQL Server还可以使用CREATE PROCERDURE语句创建存储过程。在创建存储过程之气那,应该考虑以下几个方面:
(1)在一个批处理中,CREATE PROCEDURE 语句不能与其他SQL语句合并在一起。
(2)数据库所有者具有默认的创建存储过程的权限,它可把该权限传递给其他的用户。
(3)存储过程作为数据库对象其命令必须符合标识符的命名规则。
(4)只能在当前数据库中创建属于当前数据库的存储过程。
创建存储过程语句的语法格式如下:
CREATE PROC[EDURE] procedure_name[;number]
[{@parameter data_type}
[VARYING][=default][OUTPUT]][,...n]
[WITH
{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]
[FOR REPLICATION]
AS sql_statement[,...n]
各选项的含义如下:
(1)procedure_name:新建存储过程的名称。它后面跟的可选项number是一个整数,用来区别一组同名的存储过程。存储过程的命名必须符合标识符的命名规则,在一个数据库中或对其所有者而言,存储的名字必须唯一。
(2)@parameter:存储过程的参数。在CREATE PROCEDURE语句中可以声明一个或多个参数。用户必须在执行过程时提供每个声明参数的值(除非定义了该参数的默认值)。若参数的形式以@parameter=value出现,则参数的次序可以不同,否则用户给出的参数值必须与参数列表中参数的顺序保持一致。若某一参数以@parameter=value形式给出,则其他参数必须具有相同形式。一个存储过程最多可以有2100个参数。
(3)data_type:指示参数的数据类型。所有数据类型(包括text、ntext、image)均可以用作存储过程的参数。但游标CURSOR类型只能用于OUTPUT参数,而且必须同时指定VARYING和OUTPUT关键字。
(4)default:给定参数的默认值。如果定义了默认值,则不指定该参数值仍能执行过程。默认值必须时常量或NULL。
(5)OUTPUT:表明参数是返回参数。使用OUTPUT参数可将信息返回给调用过程。
(6)RECOMPILE:表明SQL Server不保存该过程的执行计划,该过程每执行一次都要重新编译。
(7)ENCRYPTION:表示SQL Server加密syscomments表,该表中包含CREATE PROCEDURE语句的存储过程文本。使用该关键字可防止通过syscomments表来查看 存储过程内容。
(8)FOR REPLICATION:指定不能在订阅服务器上执行为复制创建的存储过程。只有在创建过滤存储过程时,才使用该选项。本选项不能和WITH RECOMPILE选项一起使用。
(9)AS sql_statement:执行过程要执行的操作,sql_statement是过程中要包含的任意数目和类型的Transact-SQL语句。
举例:创建存储过程,从表goods和表goods_classification的连接中返回商品名、商品类别、单价。
CREATE PROCEDURE goods_info AS
SELECT goods_name,classification_name,unit_price
FROM goods g INNER JOIN goods_classifcation gc
ON g.classification_id=gc.classificaton_id
存储过程创建后,存储过程的名称存放在sysobject表中,文本存放在syscomments表中。