SQL Server存储过程初学者

In this article, we will learn how to create stored procedures in SQL Server with different examples.

在本文中，我们将通过不同的示例学习如何在SQL Server中创建存储过程。

SQL Server stored procedure is a batch of statements grouped as a logical unit and stored in the database. The stored procedure accepts the parameters and executes the T-SQL statements in the procedure, returns the result set if any.

SQL Server存储过程是一组作为逻辑单元分组并存储在数据库中的语句。 存储过程接受参数并在过程中执行T-SQL语句，并返回结果集（如果有）。

To understand differences between functions and stored procedures in SQL Server, you can refer to this article, Functions vs stored procedures in SQL Server and to learn about Partial stored procedures in SQL Server, click Partial stored procedures in SQL Server.

要了解SQL Server中的函数和存储过程之间的区别，可以参考本文SQL Server中的函数与存储过程，并了解SQL Server中的部分存储过程 ，单击SQL Server中的部分存储过程 。

使用存储过程的好处 (Benefits of using a stored procedure)

It can be easily modified: We can easily modify the code inside the stored procedure without the need to restart or deploying the application. For example, If the T-SQL queries are written in the application and if we need to change the logic, we must change the code in the application and re-deploy it. SQL Server Stored procedures eliminate such challenges by storing the code in the database. so, when we want to change the logic inside the procedure we can just do it by simple ALTER PROCEDURE statement.

可以轻松修改它 ：我们可以轻松地修改存储过程中的代码，而无需重新启动或部署应用程序。 例如，如果T-SQL查询是在应用程序中编写的，并且我们需要更改逻辑，则必须更改应用程序中的代码并重新部署它。 SQL Server存储过程通过将代码存储在数据库中消除了此类挑战。 因此，当我们想要更改过程内部的逻辑时，只需执行简单的ALTER PROCEDURE语句即可。

Reduced network traffic: When we use stored procedures instead of writing T-SQL queries at the application level, only the procedure name is passed over the network instead of the whole T-SQL code.

减少网络流量：当我们使用存储过程而不是在应用程序级别编写T-SQL查询时，只有过程名称通过网络传递，而不是整个T-SQL代码传递。

Reusable: Stored procedures can be executed by multiple users or multiple client applications without the need of writing the code again.

可重用：存储过程可以由多个用户或多个客户端应用程序执行，而无需再次编写代码。

Security: Stored procedures reduce the threat by eliminating direct access to the tables. we can also encrypt the stored procedures while creating them so that source code inside the stored procedure is not visible. Use third-party tools like ApexSQL Decrypt to decrypt the encrypted stored procedures.

安全性：存储过程通过消除对表的直接访问来减少威胁。 我们还可以在创建存储过程时对其进行加密，以使存储过程中的源代码不可见。 使用ApexSQL Decrypt等第三方工具解密加密的存储过程。

Performance: The SQL Server stored procedure when executed for the first time creates a plan and stores it in the buffer pool so that the plan can be reused when it executes next time.

性能：首次执行时，SQL Server存储过程将创建一个计划并将其存储在缓冲池中，以便下次执行该计划时可以重用该计划。

I am creating sample tables that will be used in the examples in this article.

我正在创建示例表，将在本文的示例中使用。

CREATE TABLE Product
(ProductID INT, ProductName VARCHAR(100) )
GO
 
CREATE TABLE ProductDescription
(ProductID INT, ProductDescription VARCHAR(800) )
GO
 
INSERT INTO Product VALUES (680,'HL Road Frame - Black, 58')
,(706,'HL Road Frame - Red, 58')
,(707,'Sport-100 Helmet, Red')
GO
 
INSERT INTO ProductDescription VALUES (680,'Replacement mountain wheel for entry-level rider.')
,(706,'Sturdy alloy features a quick-release hub.')
,(707,'Aerodynamic rims for smooth riding.')
GO

创建一个简单的存储过程 (Creating a simple stored procedure)

We will create a simple stored procedure that joins two tables and returns the result set as shown in the following example.

我们将创建一个简单的存储过程，该过程将两个表连接起来并返回结果集，如以下示例所示。

CREATE PROCEDURE GetProductDesc
AS
BEGIN
SET NOCOUNT ON
 
SELECT P.ProductID,P.ProductName,PD.ProductDescription  FROM 
Product P
INNER JOIN ProductDescription PD ON P.ProductID=PD.ProductID
 
END

We can use ‘EXEC ProcedureName’ to execute stored procedures. When we execute the procedure GetProductDesc, the result set looks like below.

我们可以使用“ EXEC ProcedureName”来执行存储过程。 当我们执行过程GetProductDesc时，结果集如下所示。

使用参数创建存储过程 (Creating a stored procedure with parameters)

Let us create a SQL Server stored procedure that accepts the input parameters and processes the records based on the input parameter.

让我们创建一个SQL Server存储过程，该存储过程接受输入参数并根据输入参数处理记录。

Following is the example of a stored procedure that accepts the parameter.

以下是接受参数的存储过程的示例。

CREATE PROCEDURE GetProductDesc_withparameters
(@PID INT)
AS
BEGIN
SET NOCOUNT ON
 
SELECT P.ProductID,P.ProductName,PD.ProductDescription  FROM 
Product P
INNER JOIN ProductDescription PD ON P.ProductID=PD.ProductID
WHERE P.ProductID=@PID
 
END

EXEC GetProductDesc_withparameters 706

While executing the stored procedure we need to pass the input parameter. Please refer to the below image for the result set.

在执行存储过程时，我们需要传递输入参数。 请参阅下图获取结果集。

使用默认参数值创建存储过程 (Creating a stored procedure with default parameters values)

Following is the example of a stored procedure with default parameter values.

以下是带有默认参数值的存储过程的示例。

CREATE PROCEDURE GetProductDesc_withDefaultparameters
(@PID INT =706)
AS
BEGIN
SET NOCOUNT ON
 
SELECT P.ProductID,P.ProductName,PD.ProductDescription  FROM 
Product P
INNER JOIN ProductDescription PD ON P.ProductID=PD.ProductID
WHERE P.ProductID=@PID
 
END

When we execute the above procedure without passing the parameter value, the default value 706 will be used. But when executed passing the value, the default value will be ignored and the passed value will be considered as a parameter.

当我们执行上述过程而没有传递参数值时，将使用默认值706。 但是当执行传递值时，默认值将被忽略，传递的值将被视为参数。

使用输出参数创建存储过程 (Creating a stored procedure with an output parameter)

Below is the example of a stored procedure with an output parameter. The following example retrieves the EmpID which is an auto identity column when a new employee is inserted.

下面是带有输出参数的存储过程的示例。 下面的示例检索EmpID，它是插入新员工时的自动标识列。

CREATE TABLE Employee (EmpID int identity(1,1),EmpName varchar(500))

CREATE PROCEDURE ins_NewEmp_with_outputparamaters
(@Ename varchar(50),
@EId int output)
AS
BEGIN
SET NOCOUNT ON
 
INSERT INTO Employee (EmpName) VALUES (@Ename)
 
SELECT @EId= SCOPE_IDENTITY()
 
END

Executing the stored procedures with output parameters is bit different. We must declare the variable to store the value returned by the output parameter.

使用输出参数执行存储过程有些不同。 我们必须声明变量以存储输出参数返回的值。

declare @EmpID INT
 
EXEC ins_NewEmp_with_outputparamaters 'Andrew', @EmpID OUTPUT
 
SELECT @EmpID

创建一个加密的存储过程 (Creating an encrypted stored procedure)

We can hide the source code in the stored procedure by creating the procedure with the “ENCRYPTION” option.

通过使用“ ENCRYPTION”选项创建过程，我们可以在存储过程中隐藏源代码。

Following is the example of an encrypted stored procedure.

以下是加密存储过程的示例。

CREATE PROCEDURE GetEmployees
WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON 
 
SELECT EmpID,EmpName from Employee
END

When we try to view the code of the SQL Server stored procedure using sp_helptext, it returns “The text for object ‘GetEmployees’ is encrypted.”

当我们尝试使用sp_helptext查看SQL Server存储过程的代码时，它返回“对象'GetEmployees'的文本已加密。”

When you try to script the encrypted stored procedure from SQL Server management studio, it throws an error as below.

当您尝试从SQL Server Management Studio编写加密存储过程的脚本时，它将引发如下错误。

创建一个临时程序 (Creating a temporary procedure)

Like the temporary table, we can create temporary procedures as well. There are two types of temporary procedures, one is a local temporary stored procedure and another one is a global temporary procedure.

像临时表一样，我们也可以创建临时过程。 临时过程有两种类型，一种是本地临时存储过程，另一种是全局临时过程。

These procedures are created in the tempdb database.

这些过程在tempdb数据库中创建。

Local temporary SQL Server stored procedures: These are created with # as prefix and can be accessed only in the session where it created. This procedure is automatically dropped when the connection is closed.

本地临时SQL Server存储过程 ：这些存储过程以＃作为前缀创建，并且只能在其创建的会话中访问。 关闭连接后，此过程将自动删除。

Following is the example of creating a local temporary procedure.

以下是创建本地临时过程的示例。

CREATE PROCEDURE #Temp
AS
BEGIN
PRINT 'Local temp procedure'
END

Global temporary SQL Server stored procedure: These procedures are created with ## as prefix and can be accessed on the other sessions as well. This procedure is automatically dropped when the connection which is used to create the procedure is closed.

全局临时SQL Server存储过程：这些过程以##作为前缀创建，也可以在其他会话上访问。 当用于创建过程的连接关闭时，该过程将自动删除。

Below is the example of creating a global temporary procedure.

下面是创建全局临时过程的示例。

CREATE PROCEDURE ##TEMP
AS
BEGIN
PRINT 'Global temp procedure'
END

修改存储过程 (Modifying the stored procedure)

Use the ALTER PROCEDURE statement to modify the existing stored procedure. Following is the example of modifying the existing procedure.

使用ALTER PROCEDURE语句修改现有的存储过程。 以下是修改现有过程的示例。

ALTER PROCEDURE GetProductDesc
AS
BEGIN
SET NOCOUNT ON
 
SELECT P.ProductID,P.ProductName,PD.ProductDescription  FROM 
Product P
INNER JOIN ProductDescription PD ON P.ProductID=PD.ProductID
 
END

重命名存储过程 (Renaming the stored procedure)

To rename a stored procedure using T-SQL, use system stored procedure sp_rename. Following is the example that renames the procedure “GetProductDesc” to a new name “GetProductDesc_new”.

要使用T-SQL重命名存储过程，请使用系统存储过程sp_rename。 以下是将过程“ GetProductDesc”重命名为新名称“ GetProductDesc_new”的示例。

sp_rename 'GetProductDesc','GetProductDesc_new'

结论 (Conclusion)

In this article, we explored SQL Server stored procedures with different examples. In case you have any questions, please feel free to ask in the comment section below.

在本文中，我们通过不同的示例探索了SQL Server存储过程。 如果您有任何疑问，请随时在下面的评论部分中提问。

翻译自: https://www.sqlshack.com/sql-server-stored-procedures-for-beginners/