SQLServer之创建存储过程

创建存储过程注意事项

在 SQL Server、 Azure SQL Database、Azure SQL 数据仓库和并行数据库中创建 Transact-SQL 或公共语言运行时 (CLR) 存储过程，存储过程与其他编程语言中的过程类似。

可以在当前数据库中创建永久过程，或者在 tempdb 数据库中创建临时程序。

存储过程可以：

• 接受输入参数并以输出参数的格式向调用过程或批处理返回多个值。

• 包含用于在数据库中执行操作（包括调用其他过程）的编程语句。

• 向调用过程或批处理返回状态值，以指明成功或失败（以及失败的原因）。

一般备注

一个过程没有预定义的最大大小。

在过程中指定的变量可以是用户定义变量或系统变量，如 @@SPID。

第一次执行某个过程时，将编译该过程以确定检索数据的最优访问计划。 如果已经生成的计划仍保留在 数据库引擎计划缓存中，则该过程随后执行的操作可能重新使用该计划。

SQL Server 启动时可以自动执行一个或多个过程。 这些过程必须由系统管理员在 master 数据库中创建，并以 sysadmin 固定服务器角色作为后台进程执行。 这些过程不能有任何输入或输出参数。 有关详细信息，请参阅执行存储过程。

当一个过程通过引用 CLR 例程、类型或聚合来调用另一个过程或执行托管代码时，过程将被嵌套。 过程和托管代码引用的嵌套最高可达 32 级。 每当调用的过程或托管代码引用开始执行，嵌套级别就增加一级；执行完成后，嵌套级别就减少一级。 从托管代码内部调用的方法不根据嵌套级别限制进行计数。 但是，当一个 CLR 存储过程通过 SQL Server 托管访问接口执行数据访问操作时，在从托管代码到 SQL 的转换中将添加一级嵌套。

试图超过最高级的嵌套将导致整个调用链失败。 可以使用 @@NESTLEVEL 函数返回当前存储过程执行的嵌套级别。

使用SSMS数据库管理工具创建存储过程语法和T-SQL脚本创建存储过程语法相同。

使用T-SQL脚本创建存储过程

语法：

--声明数据库引用
use 数据库名;
go

--判断是否存在存储过程，如果存在则删除
if exists(select * from sys.procedures where name=存储过程名称)
drop procedure 存储过程名称;
go

--创建存储过程
create procedure [schema_name][.]procedure_name [;number]
[{ @parameter [type_schema_name.] data_type} [ null | not null ][varying] [ = default ] [ out| output ] [readonly] ] [,......n]
with [encryption][,][recompile][,][execute as clause]
for replication
as
begin
sql_statement;
end
go

语法解析：

--schema_name
--过程所属架构的名称。 过程是绑定到架构的。如果在创建过程时未指定架构名称，则自动分配正在创建过程的用户的默认架构。

--procedure_name
--过程的名称。 过程名称必须遵循有关标识符的规则，并且在架构中必须唯一。
--在命名过程时避免使用 sp_ 前缀。 此前缀由 SQL Server 用来指定系统过程。 如果存在同名的系统过程，则使用前缀可能导致应用程序代码中断。
--可在 procedure_name 前面使用一个数字符号 (#procedure_name) 来创建局部临时程序，使用两个数字符号 (##procedure_name) 来创建全局临时程序。 
--局部临时程序仅对创建了它的连接可见，并且在关闭该连接后将被删除。 全局临时程序可用于所有连接，并且在使用该过程的最后一个会话结束时将被删除。 对于 CLR 过程，不能指定临时名称。
--过程或全局临时程序的完整名称（包括 ##）不能超过 128 个字符。 局部临时程序的完整名称（包括 #）不能超过 116 个字符。

--; number
--适用范围： SQL Server 2008 到 SQL Server 2017 和 Azure SQL Database。
--用于对同名的过程分组的可选整数。 使用一个 DROP PROCEDURE 语句可将这些分组过程一起删除。

--@parameter
--在过程中声明的参数。 通过将 at 符号 (@) 用作第一个字符来指定参数名称。 参数名称必须符合标识符规则。 每个过程的参数仅用于该过程本身；其他过程中可以使用相同的参数名称。
--可声明一个或多个参数；最大值是 2,100。 除非定义了参数的默认值或者将参数设置为等于另一个参数，否则用户必须在调用过程时为每个声明的参数提供值。 
--如果过程包含表值参数，并且该参数在调用中缺失，则传入空表。 参数只能代替常量表达式，而不能用于代替表名、列名或其他数据库对象的名称。 有关详细信息，请参阅 EXECUTE (Transact-SQL)。
--如果指定了 FOR REPLICATION，则无法声明参数。

--[type_schema_name. [ =] data_type
--参数的数据类型以及该数据类型所属的架构。
--针对 Transact-SQL 过程的准则：
--所有 Transact-SQL 数据类型都可以用作参数。
--您可以使用用户定义的表类型创建表值参数。 表值参数只能是 INPUT 参数，并且这些参数必须带有 READONLY 关键字。 有关详细信息，请参阅使用表值参数（数据引擎）
--游标数据类型只能是 OUTPUT 参数，并且必须带有 VARYING 关键字。
--针对 CLR 过程的准则：
--在托管代码中具有等效值的所有本机 SQL Server 数据类型都可以用作参数。 有关 CLR 类型与 SQL Server 系统数据类型之间关系的详细信息，请参阅映射 CLR 参数数据。 有关 SQL Server 系统数据类型及其语法的详细信息，请参阅数据类型 (Transact-SQL)。
--表值或游标数据类型不能用作参数。
--如果参数的数据类型为 CLR 用户定义类型，则必须对此类型有 EXECUTE 权限。

--varying
--指定作为输出参数支持的结果集。 该参数由过程动态构造，其内容可能发生改变。 仅适用于游标参数。 该选项对于 CLR 过程无效。

--default
--参数的默认值。 如果为参数定义了默认值，则无需指定此参数的值即可执行过程。 默认值必须是常量或 NULL。 该常量值可以采用通配符的形式，这使其可以在将该参数传递到过程时使用 LIKE 关键字。
--只有 CLR 过程的默认值记录在 sys.parameters.default 列中。 对于 Transact-SQL 过程参数，该列将为 NULL。

--out|output
--指示参数是输出参数。 使用 OUTPUT 参数将值返回给过程的调用方。 除非是 CLR 过程，否则 text、ntext 和 image 参数不能用作 OUTPUT 参数。 OUTPUT 参数可以为游标占位符，CLR 过程除外。 不能将表值数据类型指定为过程的 OUTPUT 参数。

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

--encryption
--适用范围：SQL Server（ SQL Server 2008 到 SQL Server 2017）、 Azure SQL Database。
--指示 SQL Server 将 CREATE PROCEDURE 语句的原始文本转换为模糊格式。 模糊代码的输出在 SQL Server 的任何目录视图中都不能直接显示。 对系统表或数据库文件没有访问权限的用户不能检索模糊文本。 
--但是，可以通过 DAC 端口访问系统表的特权用户或直接访问数据文件的特权用户可以使用此文本。 此外，能够向服务器进程附加调试器的用户可在运行时从内存中检索已解密的过程。 有关如何访问系统元数据的详细信息，请参阅元数据可见性配置。
--该选项对于 CLR 过程无效。
--使用此选项创建的过程不能作为 SQL Server 复制的一部分发布。

--recompile
--指示 数据库引擎不缓存此过程的查询计划，这强制在每次执行此过程时都对该过程进行编译。 有关强制重新编译的原因的详细信息，请参阅重新编译存储过程。 在指定了 FOR REPLICATION 或者用于 CLR 过程时不能使用此选项。
--若要指示 数据库引擎放弃过程内单个查询的查询计划，请在该查询的定义中使用 RECOMPILE 查询提示。 有关详细信息，请参阅查询提示 (Transact-SQL)。

--execute as 子句
--指定在其中执行过程的安全上下文。
--对于本机编译存储过程（从 SQL Server 2016 (13.x) 开始和在 Azure SQL Database 中），EXECUTE AS 子句没有任何限制。 在 SQL Server 2014 (12.x) 中，对于本机编译的存储过程，支持 SELF、OWNER 和 ‘user_name’ 子句。
--有关详细信息，请参阅 EXECUTE AS 子句 (Transact-SQL)。
--SELF
--EXECUTE AS SELF 与 EXECUTE AS user_name 等价，其中指定用户是创建或更改模块的用户。 创建或更改模块的用户的实际用户 ID 存储在 sys.sql_modules 或 sys.service_queues 目录视图的 execute_as_principal_id 列中。
--OWNER
--指定模块内的语句在模块的当前所有者上下文中执行。 如果模块没有指定的所有者，则使用模块架构的所有者。 不能为 DDL 或登录触发器指定 OWNER。
--' user_name '
--指定模块内的语句在 user_name 指定的用户的上下文中执行。 将根据 user_name 来验证对模块内任意对象的权限。 不能为具有服务器作用域的 DDL 触发器或登录触发器指定 user_name。 请改用 login_name。
--user_name 必须存在于当前数据库中，并且必须是单一实例帐户。 user_name 不能为组、角色、证书、密钥或内置帐户，如 NT AUTHORITY\LocalService、NT AUTHORITY\NetworkService 或 NT AUTHORITY\LocalSystem。
--执行上下文的用户 ID 存储在元数据中，可以在 sys.sql_modules 或 sys.assembly_modules 目录视图的 execute_as_principal_id 列查看。
--' login_name '
--指定模块内的语句在 login_name 指定的 SQL Server 登录的上下文中执行。 将根据 login_name 来验证对模块内任意对象的权限。 只能为具有服务器作用域的 DDL 触发器或登录触发器指定 login_name。
--login_name 不能为组、角色、证书、密钥或内置帐户，如 NT AUTHORITY\LocalService、NT AUTHORITY\NetworkService 或 NT AUTHORITY\LocalSystem。

--for replication
--适用范围：SQL Server（ SQL Server 2008 到 SQL Server 2017）、 Azure SQL Database。
--指定为复制创建该过程。 因此，它不能在订阅服务器上执行。 使用 FOR REPLICATION 选项创建的过程可用作过程筛选器，且仅在复制过程中执行。 如果指定了 FOR REPLICATION，则无法声明参数。 对于 CLR 过程，不能指定 FOR REPLICATION。 对于使用 FOR REPLICATION 创建的过程，忽略 RECOMPILE 选项。
--FOR REPLICATION 过程在 sys.objects 和 sys.procedures 中包含 RF 对象类型。

--{ [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
--构成过程主体的一个或多个 Transact-SQL 语句。 您可以使用可选的 BEGIN 和 END 关键字将这些语句括起来。 有关信息，请参阅后面的“最佳实践”、“一般备注”以及“限制和局限”部分。

示例：

--声明数据库引用
use testss;
go

--判断是否存在存储过程，如果存在则删除
if exists(select * from sys.procedures where name='noreference')
drop procedure noreference;
go

--创建存储过程
create procedure noreference
;1
@testid int=NULL,@outparameter varchar(100) out
with encryption,recompile,execute as owner
--for replication
as
begin
if @testid is not null 
begin
select * from test1 where id=@testid;
set @outparameter='有参数指定输出';
end
else
begin
select * from test1;
set @outparameter='无参数输出全部';
end
end
go

示例结果：截图依次显示创建结果和调用结果。