过程（也称为存储过程）是包含在 CREATE PROCEDURE / PROC 语句中的 SQL 语句的集合。过程始终包含名称、参数列表和 SQL 语句。它还可能包含条件语句，如 IF 或 CASE 或循环。可以通过使用过程、其他过程和应用程序来调用过程。

存储过程功能

  • 存储过程提高了应用程序的性能。存储过程一旦创建，就会被编译并存储在数据库中。
  • 存储过程减少了应用程序和数据库服务器之间的流量。因为应用程序只需发送存储过程的名称和参数，而不是发送多个 SQL 语句。
  • 存储过程是可重用的。业务逻辑可以在存储过程中实现，该存储过程可以被应用程序多次使用，或者可以被应用程序的不同模块使用。这使得数据库更加一致。如果需要任何更改，只能在存储过程中进行。
  • 存储过程比 AdHoc 查询更安全。可以授予用户执行存储过程的权限，而不授予存储过程中使用的表的权限。存储过程有助于防止数据库受到 SQL 注入。

CREATE PROCEDURE

CREATE PROCEDURE 或 CREATE PROC 语句用于创建存储过程。默认情况下，存储例程与默认数据库关联。要将例程与给定数据库显式关联，请在创建时将名称指定为schema_name.procedure_name。

存储过程还可以执行另一个存储过程或函数模块化代码。

在 SQL Server (Transact-SQL) 中使用 CREATE PROCEDURE 语句的语法如下:

CREATE { PROCEDURE | PROC } [schema_name.]procedure_name
  [ { @parameter [type_schema_name.] datatype }
    [ VARYING ] [ = default ] [ OUT | OUTPUT | READONLY ]
  ] [ ,...n ]

  [ WITH { ENCRYPTION | RECOMPILE | EXECUTE AS Clause } ]
  [ FOR REPLICATION ]

  AS

  BEGIN 
    -- SQL 语句
  END;

参数

schema_name指定拥有存储过程的架构名称。
procedure_name指定分配给此过程的名称。
@parameter指定一个或更多参数传递到过程中。
type_schema_name指定参数的数据类型以及数据所使用的模式类型所属。
datatype指定@parameter的数据类型。
VARYING当结果集是输出参数时指定游标参数。
default 指定分配给@parameter的默认值。
OUT表示@parameter是输出参数。
OUTPUT表示@parameter是输出参数。
READONLY表示@parameter不能被存储过程覆盖。
ENCRYPTION表示存储过程的源不会以纯文本形式存储在 SQL Server 的系统视图中。
RECOMPILE表示不会为此存储过程缓存查询计划。
EXECUTE AS Clause 设置安全上下文以执行存储过程。
FOR REPLICATION表示存储过程仅在复制期间执行。

要调用存储过程，请使用 EXEC 或 EXECUTE 语句。请参阅以下语法:

EXEC procedure_name;

或

EXECUTE procedure_name;

创建不带参数的过程

考虑一个名为 Employee 的数据库表，其中包含以下记录:

EmpID     Name      City         Age    Salary 
--------  --------  -----------  -----  --------
1         John      London       25     3000   
2         Marry     New York     24     2750   
3         Jo        Paris        27     2800   
4         Kim       Amsterdam    30     3100   
5         Ramesh    New Delhi    28     3000   
6         Huang     Beijing      28     2800

要获取此表中 Salary 大于或等于 3000 的所有记录并计算所有表行数，可以使用以下代码创建一个名为 high_salary_employees 的过程:

CREATE PROCEDURE high_salary_employees
AS
BEGIN
  SELECT * FROM Employee WHERE Salary >= 3000;  
END;  
GO;

成功执行后，可以按如下方式调用该过程:

EXEC high_salary_employees;

这将产生如下所示的结果:

EmpID     Name      City         Age    Salary 
--------  --------  -----------  -----  --------
1         John      London       25     3000    
4         Kim       Amsterdam    30     3100   
5         Ramesh    New Delhi    28     3000    

(3 row(s) affected)

使用一个参数创建过程

在此过程中，使用了一个名为"var1"的整数类型参数，该参数从用户处获取值。其主体部分使用 SELECT 语句从表中获取记录，并仅返回用户提供的行。

CREATE PROCEDURE get_employees @var1 INT
AS
BEGIN
  SELECT TOP @var1 * FROM Employee; 
END; 
GO;

成功执行后，可以按如下方式调用该过程:

EXEC get_employees @var1 = 3;

这将产生如下所示的结果:

EmpID     Name     City        Age    Salary 
--------  -------  ----------  -----  --------
1         John     London      25     3000   
2         Marry    New York    24     2750   
3         Jo       Paris       27     2800    

(3 row(s) affected)

创建具有两个参数的过程

在此过程中，名为"salary"和"age"的两个整数类型参数是用过的。该过程使用这些变量的值从表中获取记录。

CREATE PROCEDURE employees_salary_age @age INT, @salary INT
AS
BEGIN
  SELECT * FROM Employee
  WHERE Salary >= @salary AND Age <= @age; 
END; 
GO;

成功执行后，可以按如下方式调用该过程:

EXEC employees_salary_age @salary =2800, @age = 28;

这将产生如下结果:如下所示:

EmpID     Name      City         Age    Salary 
--------  --------  -----------  -----  --------
1         John      London       25     3000     
3         Jo        Paris        27     2800    
5         Ramesh    New Delhi    28     3000   
6         Huang     Beijing      28     2800     

(4 row(s) affected)

删除过程

在 SQL Server (Transact-SQL) 中创建过程后，可以使用 DROP PROCEDURE 语句将其删除.

语法

在 SQL Server (Transact-SQL) 中删除过程的语法如下:

DROP { PROC | PROCEDURE } [IF EXISTS] [schema_name.]procedure_name;

IF EXISTS 为一个可选参数，仅当数据库中存在过程时，才有条件地删除该过程。如果删除不存在的过程，则会引发错误。

schema_name 是可选的。如果省略，则该过程将从默认数据库中删除。上述语句删除名为 procedure_name 的过程。

示例:

要删除名为 CalcMaxSalary 的过程，以下语句可以使用:

DROP PROCEDURE CalcMaxSalary;