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 列查看。
sql存储过程实例--' 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
⽰例结果：截图依次显⽰创建结果和调⽤结果。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。