SQL Server创建存储过程|极客笔记

SQL Server创建存储过程

介绍

存储过程是SQL Server中的一种对象，它是一个预编译的SQL语句集合，可以接受输入参数，并且可以返回一个或多个结果集。存储过程可以将一系列的操作集合在一起，简化代码的编写和维护，并且可以提高数据库的性能。

本文将详细介绍如何在SQL Server中创建存储过程，包括存储过程的特点、创建存储过程的语法、使用输入参数和返回结果集等内容。

存储过程的特点

存储过程有以下几个特点：

预编译：存储过程在第一次执行时被编译，以后的执行将不会再次编译，减少了编译和解析的开销。
可重用性：存储过程可以在多个地方被调用，避免了重复编写相同的SQL语句。
安全性：存储过程可以通过授权控制访问，避免了直接操作表的权限问题。
性能优化：通过存储过程，可以将一次性耗时较长的操作放在服务器端执行，减少了网络传输的开销，提高了性能。

创建存储过程的语法

创建存储过程的语法如下：

CREATE PROCEDURE procedure_name
    [ @parameter1 datatype [ VARYING ] [ = default ] [ OUTPUT ]
    ...
]
[ WITH [ EXECUTE AS Clause ] ]
[ [ , ] [ RECOMPILE | ENCRYPTION | SCHEMABINDING | EXECUTE AS Clause ] ... ]
AS
    [ BEGIN ]
    SQL_statement [ ...n ]
    [ END ]

其中：

procedure_name：表示存储过程的名称。
@parameter1 datatype [ VARYING ] [ = default ] [ OUTPUT ]：表示存储过程的输入参数，可以有多个参数，每个参数的名称、数据类型、是否有默认值和是否是输出参数可以按需指定。
AS：表示存储过程的开始。
SQL_statement：表示存储过程执行的SQL语句。
BEGIN和END：表示存储过程的执行块，可以省略，如果省略，则表示只有一个语句。

创建一个简单的存储过程

下面我们通过一个简单的示例来演示如何创建一个存储过程。

假设我们有一个名为Employees的表，包含以下列：

EmployeeID：员工ID，整数类型。
FirstName：员工的名字，字符串类型。
LastName：员工的姓氏，字符串类型。
Salary：员工的薪水，整数类型。

我们需要根据员工ID查询员工的信息。我们可以创建一个存储过程来实现这个功能。

首先，在SQL Server管理工具中，打开一个新的查询窗口，然后执行以下代码：

CREATE PROCEDURE GetEmployeeInfo
    @employee_id INT
AS
    SELECT * FROM Employees WHERE EmployeeID = @employee_id

上述代码创建了一个名为GetEmployeeInfo的存储过程，它接受一个输入参数@employee_id，并根据该参数查询员工信息。

接下来，我们可以通过以下代码调用存储过程并传入参数：

EXEC GetEmployeeInfo @employee_id = 2

执行结果将返回Employees表中EmployeeID为2的员工的信息。

使用输入参数

存储过程可以接受输入参数，用于根据不同的条件查询不同的结果。输入参数可以按需指定。

在前面的示例中，我们已经演示了如何使用一个输入参数。下面我们将演示如何使用多个输入参数。

假设我们需要根据员工的名字和姓氏来查询员工的信息。我们可以修改存储过程的代码如下：

CREATE PROCEDURE GetEmployeeInfo
    @first_name VARCHAR(50),
    @last_name VARCHAR(50)
AS
    SELECT * FROM Employees WHERE FirstName = @first_name AND LastName = @last_name

上述代码创建了一个名为GetEmployeeInfo的存储过程，它接受两个输入参数@first_name和@last_name，并根据这两个参数查询员工信息。

接下来，我们可以通过以下代码调用存储过程并传入参数：

EXEC GetEmployeeInfo @first_name = 'John', @last_name = 'Doe'

执行结果将返回Employees表中名为’John Doe’的员工的信息。

返回结果集

存储过程除了可以接受输入参数外，还可以返回一个或多个结果集。下面我们将演示如何返回结果集。

假设我们需要返回Employees表中薪水超过某个指定金额的员工的信息。我们可以修改存储过程的代码如下：

CREATE PROCEDURE GetHighPaidEmployees
    @salary INT
AS
BEGIN
    SELECT * FROM Employees WHERE Salary > @salary
    SELECT COUNT(*) AS Total FROM Employees WHERE Salary > @salary
END

上述代码创建了一个名为GetHighPaidEmployees的存储过程，它接受一个输入参数@salary，并根据该参数查询薪水超过指定金额的员工信息。同时，它还返回一个结果集，包含薪水超过指定金额的员工的总数。

接下来，我们可以通过以下代码调用存储过程并传入参数：

EXEC GetHighPaidEmployees @salary = 5000

执行结果将返回薪水超过5000的员工的信息和总数。

总结

本文详细介绍了如何在SQL Server中创建存储过程，包括存储过程的特点、创建存储过程的语法、使用输入参数和返回结果集等内容。通过使用存储过程，我们可以将一系列的操作集合在一起，简化代码的编写和维护，并且可以提高数据库的性能。

SQL Server创建存储过程