sql server 2005数据存储过程学习笔记

sql server 2005 存储过程语法

CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]
[ { @parameter [ type_schema_name. ] data_type }
sql server 2005 存储过程

语法

CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]
    [ { @parameter [ type_schema_name. ] data_type }
        [ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]
    ] [ ,...n ]
[ WITH [ ,...n ] ]
[ FOR REPLICATION ]
AS { [;][ ...n ] | }
[;]
::=
    [ ENCRYPTION ]
    [ RECOMPILE ]
    [ EXECUTE AS Clause ]

::=
{ [ BEGIN ] statements [ END ] }

::=

EXTERNAL NAME assembly_name.class_name.method_name

sql存储过程创建方法

--创建数据表
6> CREATE TABLE employee(
7>    id          INTEGER NOT NULL PRIMARY KEY,
8>    first_name  VARCHAR(10),
9>    last_name   VARCHAR(10),
10>    salary      DECIMAL(10,2),
11>    start_Date  DATETIME,
12>    region      VARCHAR(10),
13>    city        VARCHAR(20),
14>    managerid   INTEGER
15> );
16> GO
1> INSERT INTO employee VALUES (1, 'Jason' ,  'Martin', 5890,'2005-03-22','North','Vancouver',3);
2> GO

(1 rows affected)
1> INSERT INTO employee VALUES (2, 'Alison',  'Mathews',4789,'2003-07-21','South','Utown',4);
2> GO

(1 rows affected)
1> INSERT INTO employee VALUES (3, 'James' ,  'Smith',  6678,'2001-12-01','North','Paris',5);
2> GO

(1 rows affected)
1> INSERT INTO employee VALUES (4, 'Celia' ,  'Rice',   5567,'2006-03-03','South','London',6);
2> GO

(1 rows affected)
1> INSERT INTO employee VALUES (5, 'Robert',  'Black',  4467,'2004-07-02','East','Newton',7);
2> GO

(1 rows affected)
1> INSERT INTO employee VALUES (6, 'Linda' ,  'Green' , 6456,'2002-05-19','East','Calgary',8);
2> GO

(1 rows affected)
1> INSERT INTO employee VALUES (7, 'David' ,  'Larry',  5345,'2008-03-18','West','New York',9);
2> GO

(1 rows affected)
1> INSERT INTO employee VALUES (8, 'James' ,  'Cat',    4234,'2007-07-17','West','Regina',9);
2> GO

(1 rows affected)
1> INSERT INTO employee VALUES (9, 'Joan'  ,  'Act',    6123,'2001-04-16','North','Toronto',10);
2> GO

(1 rows affected)
1>
2> select * from employee;
3> GO
id          first_name last_name  salary       start_Date              region     city                 managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
          1 Jason      Martin          5890.00 2005-03-22 00:00:00.000 North      Vancouver                      3
          2 Alison     Mathews         4789.00 2003-07-21 00:00:00.000 South      Utown                          4
          3 James      Smith           6678.00 2001-12-01 00:00:00.000 North      Paris                          5
          4 Celia      Rice            5567.00 2006-03-03 00:00:00.000 South      London                         6
          5 Robert     Black           4467.00 2004-07-02 00:00:00.000 East       Newton                         7
          6 Linda      Green           6456.00 2002-05-19 00:00:00.000 East       Calgary                        8
          7 David      Larry           5345.00 2008-03-18 00:00:00.000 West       New York                       9
          8 James      Cat             4234.00 2007-07-17 00:00:00.000 West       Regina                         9
          9 Joan       Act             6123.00 2001-04-16 00:00:00.000 North      Toronto                       10

(9 rows affected)

--创建sql存储教程
3> CREATE PROCEDURE spInsertOrUpdateEmployee
4> @FirstName nVarChar(50),
5> @LastName nVarChar(25),
6> @Salary Money
7> AS
8> IF EXISTS(SELECT * From Employee Where First_name = @FirstName)
9> UPDATE Employee SET Last_NAME = @LastName, Salary = @Salary
10> WHERE First_name = @FirstName
11> ELSE
12> INSERT INTO Employee (ID,First_Name, Last_Name, Salary)
13> SELECT 99, @FirstName, @LastName, @Salary
14> GO

--删除表
5> drop table employee;
6> GO

schema_name

过程所属架构的名称。

procedure_name

新存储过程的名称。过程名称必须遵循有关标识符的规则，并且在架构中必须唯一。

极力建议不在过程名称中使用前缀 sp_。此前缀由 SQL Server 使用，以指定系统存储过程。有关详细信息，请参阅创建存储过程（数据库引擎）。

可在 procedure_name 前面使用一个数字符号 (#) (#procedure_name) 来创建局部临时过程，使用两个数字符号 (##procedure_name) 来创建全局临时过程。对于 CLR 存储过程，不能指定临时名称。

存储过程或全局临时存储过程的完整名称（包括 ##）不能超过 128 个字符。局部临时存储过程的完整名称（包括 #）不能超过 116 个字符。

;number

是可选整数，用于对同名的过程分组。使用一个 DROP PROCEDURE 语句可将这些分组过程一起删除。例如，称为 orders 的应用程序可能使用名为 orderproc;1、orderproc;2 等的过程。DROP PROCEDURE orderproc 语句将删除整个组。如果名称中包含分隔标识符，则数字不应包含在标识符中；只应在 procedure_name 前后使用适当的分隔符。

带编号的存储过程有以下限制：

不能使用 xml 或 CLR 用户定义类型作为数据类型。

不能对带编号的存储过程创建计划指南。

注意：
后续版本的 Microsoft SQL Server 将删除该功能。请避免在新的开发工作中使用该功能，并着手修改当前还在使用该功能的应用程序。

@parameter
过程中的参数。在 CREATE PROCEDURE 语句中可以声明一个或多个参数。除非定义了参数的默认值或者将参数设置为等于另一个参数，否则用户必须在调用过程时为每个声明的参数提供值。存储过程最多可以有 2,100 个参数。如果过程包含表值参数，并且该参数在调用中缺失，则传入空表默认值。

通过将 at 符号 (@) 用作第一个字符来指定参数名称。参数名称必须符合有关标识符的规则。每个过程的参数仅用于该过程本身；其他过程中可以使用相同的参数名称。默认情况下，参数只能代替常量表达式，而不能用于代替表名、列名或其他数据库对象的名称。有关详细信息，请参阅 EXECUTE (Transact-SQL)。

如果指定了 FOR REPLICATION，则无法声明参数。

[ type_schema_name. ] data_type
参数以及所属架构的数据类型。所有数据类型都可以用作 Transact-SQL 存储过程的参数。可以使用用户定义表类型来声明表值参数作为 Transact-SQL 存储过程的参数。只能将表值参数指定为输入参数，这些参数必须带有 READONLY 关键字。cursor 数据类型只能用于 OUTPUT 参数。如果指定了 cursor 数据类型，则还必须指定 VARYING 和 OUTPUT 关键字。可以为 cursor 数据类型指定多个输出参数。

对于 CLR 存储过程，不能指定 char、varchar、text、ntext、image、cursor、用户定义表类型和 table 作为参数。有关 CLR 类型与 SQL Server 系统数据类型之间关系的详细信息，请参阅 映射 CLR 参数数据。有关 SQL Server 系统数据类型及其语法的详细信息，请参阅数据类型 (Transact-SQL)。

如果参数的数据类型为 CLR 用户定义类型，则必须对此类型有 EXECUTE 权限。

如果未指定 type_schema_name，则 SQL Server 数据库引擎将按以下顺序引用 type_name：

SQL Server 系统数据类型。

当前数据库中当前用户的默认架构。

当前数据库中的 dbo 架构。

对于带编号的存储过程，数据类型不能为 xml 或 CLR 用户定义类型。

VARYING
指定作为输出参数支持的结果集。该参数由存储过程动态构造，其内容可能发生改变。仅适用于 cursor 参数。

default
参数的默认值。如果定义了 default 值，则无需指定此参数的值即可执行过程。默认值必须是常量或 NULL。如果过程使用带 LIKE 关键字的参数，则可包含下列通配符：%、_、[] 和 [^]。

注意：
只有 CLR 过程的默认值记录在 sys.parameters.default 列中。对于 Transact-SQL 过程参数，该列将为 NULL。

OUTPUT
指示参数是输出参数。此选项的值可以返回给调用 EXECUTE 的语句。使用 OUTPUT 参数将值返回给过程的调用方。除非是 CLR 过程，否则 text、ntext 和 image 参数不能用作 OUTPUT 参数。使用 OUTPUT 关键字的输出参数可以为游标占位符，CLR 过程除外。不能将用户定义表类型指定为存储过程的 OUTPUT 参数。

READONLY
指示不能在过程的主体中更新或修改参数。如果参数类型为用户定义的表类型，则必须指定 READONLY。

RECOMPILE
指示数据库引擎不缓存该过程的计划，该过程在运行时编译。如果指定了 FOR REPLICATION，则不能使用此选项。对于 CLR 存储过程，不能指定 RECOMPILE。

若要指示数据库引擎放弃存储过程内单个查询的计划，请使用 RECOMPILE 查询提示。有关详细信息，请参阅查询提示 (Transact-SQL)。如果非典型值或临时值仅用于属于存储过程的查询子集，则使用 RECOMPILE 查询提示。

ENCRYPTION
指示 SQL Server 将 CREATE PROCEDURE 语句的原始文本转换为模糊格式。模糊代码的输出在 SQL Server 的任何目录视图中都不能直接显示。对系统表或数据库文件没有访问权限的用户不能检索模糊文本。但是，可以通过 DAC 端口访问系统表的特权用户或直接访问数据文件的特权用户可以使用此文本。此外，能够向服务器进程附加调试器的用户可在运行时从内存中检索已解密的过程。有关访问系统元数据的详细信息，请参阅元数据可见性配置。

该选项对于 CLR 存储过程无效。

使用此选项创建的过程不能在 SQL Server 复制过程中发布。

EXECUTE AS
指定在其中执行存储过程的安全上下文。

有关详细信息，请参阅 EXECUTE AS 子句 (Transact-SQL)。

FOR REPLICATION
指定不能在订阅服务器上执行为复制创建的存储过程。使用 FOR REPLICATION 选项创建的存储过程可用作存储过程筛选器，且只能在复制过程中执行。如果指定了 FOR REPLICATION，则无法声明参数。对于 CLR 存储过程，不能指定 FOR REPLICATION。对于使用 FOR REPLICATION 创建的过程，忽略 RECOMPILE 选项。

FOR REPLICATION 过程将在 sys.objects 和 sys.procedures 中包含 RF 对象类型。

要包含在过程中的一个或多个 Transact-SQL 语句。有关某些适用的限制的信息，请参阅“备注”部分。

EXTERNAL NAME assembly_name.class_name.method_name
指定 .NET Framework 程序集的方法，以便 CLR 存储过程引用。class_name 必须为有效的 SQL Server 标识符，并且该类必须存在于程序集中。如果类包含一个使用句点 (.) 分隔命名空间各部分的限定命名空间的名称，则必须使用方括号 ([]) 或引号 ("") 将类名称分隔开。指定的方法必须为该类的静态方法。

注意：

默认情况下，SQL Server 不能执行 CLR 代码。可以创建、修改和删除引用公共语言运行时模块的数据库对象；不过，只有在启用 clr enabled 选项之后，才能在 SQL Server 中执行这些引用。若要启用该选项，请使用 sp_configure。

 
--调用系统存储过程xp_cmdshell
exec xp_cmdshell 'md d:/temp',output

create proc test
@a int, @b int
as
select * from T_Person

go
exec test

--参数全为输入参数
if object_id('test','p') is not null
drop proc test
create proc test
@a int, @b int
as
select @a+@b

exec test @b=2,@a=12

--参数带输出参数
if object_id('test','p') is not null
drop proc test
create proc test
@a int, @b int,@c int output
as
set @c=@a+@b

go
declare @temp int
exec test 12,23,@temp output
select @temp