SQLServer之修改存储过程

修改存储过程注意事项

只能修改先前在 sql server 中通过执行 create procedure 语句创建的过程。

transact-sql 存储过程修改为 clr 存储过程，反之亦然。

alter procedure 不会更改权限，也不影响相关的存储过程或触发器。 但是，当修改存储过程时，quoted_identifier 和 ansi_nulls 的当前会话设置包含在该存储过程中。 如果设置不同于最初创建存储过程时有效的设置，则存储过程的行为可能会更改。

如果原来的过程定义是使用 with encryption 或 with recompile 创建的，那么只有在 alter procedure 中也包含这些选项时，这些选项才有效。

使用ssms数据库管理工具和t-sql脚本修改存储过程语法一致。

使用t-sql脚本修改存储过程

语法：

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

--修改存储过程

alter 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

--修改存储过程
alter procedure [dbo].[noreference]
;1
@inputparamter int=null,@outputparamter nvarchar(100)=null output
with encryption,recompile,execute as owner
--for replication
as
begin
if @inputparamter is not null
begin
select * from test1 where id=@inputparamter;
set @outputparamter='我是修改过的有参数的输出指定值的存储过程';
end
else
begin
select * from test1;
set @outputparamter='我是修改过的无参数的输出全部值的存储过程';
end
end
go

示例结果：本示例演示如何调用存储过程和存储过程执行结果。

1、连接数据库-》选择数据库-》展开数据库-》展开可编程性-》展开存储过程-》选择存储过程-》右键点击-》选择执行存储过程。

2、在执行存储过程弹出框-》输入存储过程参数-》点击确定。

3、存储过程执行结果。