SQL SERVER中存储过程的使用场景

存储过程的使用场景

• 当 一个事务涉及到多个SQL语句时或者涉及到对多个表的操作时就要考虑用存储过程;
• 当在一个事务的完成需要很复杂的商业逻辑时(比如，对多个数据的操作，对多个状态的判断更改等)要考虑;
• 还有就是比较复杂的统计和汇总也要考虑。

使用存储过程的优点和缺点

优点：

• 减少了服务器/客户端网络流量

过程中的命令作为代码的单个批处理执行。 这可以显著减少服务器和客户端之间的网络流量，因为只有对执行过程的调用才会跨网络发送。 如果没有过程提供的代码封装，每个单独的代码行都不得不跨网络发送。

• 更强的安全性

1、恶意用户看不到表和数据库对象名称、嵌入自己的 Transact-SQL 语句或搜索关键数据。
2、使用过程参数有助于避免 SQL 注入攻击。由于参数输入被视为文本值而不是可执行代码，因此攻击者很难将命令插入 Transact-SQL 语句

• 代码的重复使用

任何重复的数据库操作的代码都非常适合于在过程中进行封装。 这消除了不必要地重复编写相同的代码、降低了代码不一致性

• 更容易维护

对于基础数据库中的任何更改，只有过程是必须更新的。 应用程序层保持独立，并且不必知道对数据库布局、关系或进程的任何更改的情况。

• 提高了性能

默认情况下，在首次执行过程时将编译过程，并且创建一个执行计划，供以后的执行重复使用。 因为查询处理器不必创建新计划，所以，它通常用更少的时间来处理过程。
注意：如果过程引用的表或数据有显著变化，则预编译的计划可能实际上会导致过程的执行速度减慢。 在此情况下，重新编译过程和强制新的执行计划可提高性能。

缺点：

• 随着SQL行数的增加，维护复杂度呈线性提升
• 无法调试，迭代过程中风险较高
• 过多的使用存储过程会降低系统的移植性

存储过程的 Transact-SQL 语法

CREATE [ OR ALTER ] { PROC | PROCEDURE } [schema_name.] procedure_name
    [ { @parameter data_type } [ NULL | NOT NULL ] [ = default ]
        [ OUT | OUTPUT ] [READONLY]
    ] [ ,... n ]
  WITH NATIVE_COMPILATION, SCHEMABINDING [ , EXECUTE AS clause ]
AS
{
  BEGIN ATOMIC WITH ( <set_option> [ ,... n ] )
sql_statement [;] [ ... n ]
[ END ]
}
[;]
1
2
3
4
5
6
7
8
9
10
11
12

创建存储过程

使用 Transact-SQL创建存储过程（修改存储过程把CREATE改为ALTER）

CREATE PROCEDURE HumanResources.uspGetEmployeesTest2   
    @LastName nvarchar(50),   
    @FirstName nvarchar(50)   
AS   

    SET NOCOUNT ON;  
    SELECT FirstName, LastName, Department  
    FROM HumanResources.vEmployeeDepartmentHistory  
    WHERE FirstName = @FirstName AND LastName = @LastName  
    AND EndDate IS NULL; 
1
2
3
4
5
6
7
8
9
10

执行存储过程

EXECUTE HumanResources.uspGetEmployeesTest2 N'Ackerman', N'Pilar';  
-- Or  
EXEC HumanResources.uspGetEmployeesTest2 @LastName = N'Ackerman', @FirstName = N'Pilar';  
1
2
3

删除存储过程

DROP PROCEDURE [<stored procedure name>]
1

重命名存储过程

EXEC sp_rename 'old_procedurename', 'new_procedurename'; 
1