SQL Server存储过程

一、存储过程的作用

        为方便理解，可以认为存储过程的本质是一组储存在数据库的SQL语句，多条SQL语句组合实现某种功能。存储过程一般用于复杂的 SELECT 语句，不适合复杂的 INSERT、UPDATE 操作。

二、存储过程的好处

1、减少网络流量

        后端服务器调用数据库中的存储过程时，只需要传递储存过程需要的参数和返回结果，不用传递整个SQL语句。减少了从后端服务器发送到服务器的SQL语句数量，从而降低了网络传输的数据量。

2、提升性能

        存储过程在首次执行时会被编译成任务存储在数据库中，后续调用该存储过程时不需要重新编译而是直接调用编译好的任务。SQL脚本和后端代码中编写的SQL语句每次被执行时都需要重新编译，相比之下存储过程的性能更高。

3、更强的安全性

        例如要执行截断表 TRUNCATE TABLE 操作，用户需要具有 ALTER 权限。但是如果直接给用户授予 ALTER 权限，用户同时也拥有了超出截断表能力的权限。存储过程通过将 TRUNCATE TABLE 语句纳入模块中，用户需要截断表时调用存储过程，以此避免直接给用户授予过大的权限保证数据库安全。

三、创建存储过程

1、创建步骤

• 展开所需的数据库，然后展开“可编程性”。
• 右键单击“存储过程”，然后选择“存储过程”。 此时会打开一个新的查询窗口，其中包含存储过程的模板。

• 默认的存储过程模板具有两个参数，在 “查询” 菜单上，选择 “指定模板参数的值”可以调整参数模板。

• 生成的存储过程模板如下：

• 若要测试语法，可以点击“查询”菜单，选择“分析”。 

• 在工具栏中选择“执行”，即可成功创建存储过程。

• 执行存储过程时，右键单击存储过程名称，选择“执行存储过程”。

2、代码释义

CREATE PROCEDURE test 

创建一个存储过程，将该存储过程命名为test

@p1 int = 0,

@p1：是参数1的变量名，数据库中自定义的变量名以"@"开头。

int：是参数1的数据类型。

=0：表示参数1的默认值为0，没有默认值时可以省略。

AS
BEGIN	

END

以上三个关键字标识存储过程的主体部分。

SET NOCOUNT ON;

默认情况下，每执行一个查询(如SELECT、INSERT、DELETE等)SQL Server都会返回受影响的行数。 NOCOUNT设置为ON时，SQL Server 不会返回受影响的行数，确保存储过程不会提前返回。

SET ANSI_NULLS ON

控制 SQL Server 如何与 NULL 值进行比较。

• 当 ANSI_NULLS 为设置为 ON 时，NULL==NULL 返回 unknow 而不是 true。
• 当 ANSI_NULLS 为设置为 OFF 时，NULL==NULL 会返回 true。

SET QUOTED_IDENTIFIER ON

当 QUOTED_IDENTIFIER 为 ON 时。

• 双引号 ("") 被解释为标识符定界符。例如，"MyColumn" 引用了一个名为 MyColumn 的列。
• 单引号 ('') 被解释为字符串定界符。例如，'Hello, World!' 是一个字符串。
• 在创建或修改表、列、索引等对象时，必须使用方括号 ([]) 或双引号 ("") 来引用包含特殊字符或保留字的标识符。

当 QUOTED_IDENTIFIER 为 OFF 时。

• 双引号 ("") 被解释为字符串定界符，就像单引号一样。
• 标识符（如表名、列名等）不需要被引用，除非它们包含特殊字符或保留字。在这种情况下，可以使用方括号 ([]) 来引用它们。

推荐将 QUOTED_IDENTIFIER 设置为 ON，保持标识符引用一致。

四、存储过程的参数

1、输入参数

（1）按顺序为输入参数传值

• 执行存储过程时，按顺序为参数1、参数2赋予常量值。

USE Db;  
GO
  
EXEC dbo.test 1, 2;  
GO  

• 执行存储过程时，按顺序为参数1、参数2赋予变量值。

USE Db
GO

DECLARE @Value1 int, @Value2 int;  
SET @Value1 = 1;  
SET @Value2= 2;  
EXEC dbo.test @Value1, @Value2;  
GO

注意：不能直接将函数赋值给存储过程参数。

（2）指定输入参数名称传值

指定参数名称的方式传值，不受参数顺序和参数默认值的影响。

USE Db
GO

DECLARE @Value1 int, @Value2 int;  
SET @Value1 = 1;  
SET @Value2= 2;  
EXEC dbo.test @p1 = @Value1, @p2 = @Value2;  
GO

        存储过程的参数可能存在默认值，调用存储过程时不为该参数赋值，会默认该参数使用默认值。当具有默认值的参数和其他参数同时使用时，按顺序为参数赋值可能出现混乱。因此推荐用指定参数名称的方式为参数赋值。

2、输出参数

        在创建存储过程时使用 OUT 关键字可以将参数定义为输出参数。输出参数类似于方法中的引用传递参数，在方法体中改变形参的值，方法体外的实参也会随之改变。

• 创建存储过程

USE DB
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE PRO_add
@p1 int, @p2 int, @p3 int OUT
AS
BEGIN
	SET NOCOUNT ON;
    SET @p3 = @p1 + @p2;
END
GO

• 执行存储过程

DECLARE @result int=0;
EXEC dbo.PRO_add @p1 = 1, @p2 = 2, @p3 = @result OUT;
PRINT @result;
GO

• 输出结果

五、存储过程返回数据

1、返回结果集

        存储过程的正文中包含 SELECT 语句，则 SELECT 语句指定的字段将返回给调用存储过程的客户端。 

2、返回状态码

        存储过程可以返回一个整数值，以指示存储过程的执行状态。 使用 RETURN 语句指定过程的返回代码。 与输出参数一样，存储过程执行时必须将状态码保存到变量中，才能使用返回的状态码。

• 创建存储过程

CREATE PROCEDURE my_proc
    @p1 int;
AS
BEGIN
    IF @p1 IS NULL
    BEGIN
        RETURN (1)
    END

    ELSE IF @P1<0
    BEGIN
        RETURN (2)
    END

    ElSE RETURN (3)
END

• 执行存储过程

DECLARE @result int;
EXECUTE @result = my_proc @p1=10;
PRINT @result
GO

• 输出结果

​

六、重新编译

        默认情况下存储过程只在首次执行时进行编译，后续表存储过程涉及的表结构发生变化时可能需要重新编译存储过程。

• 创建存储过程时声明每次执行都重新编译

CREATE PROCEDURE my_proc
WITH RECOMPILE
AS
BEGIN
    --省略
END

• 使用系统存储过程对指定存储过程重新编译

EXEC sp_recompile N'dbo.myproc';   

七、外部代码实现存储过程

        为了方便理解，上文我们简单地认为存储过程是一组SQL语句。实际上存储过程支持拓展，可以调用其他语言编写的程序来实现存储过程。 以下是使用.NET Framework 公共语言运行时(CLR)实现存储过程的例子。

• 编写程序并编译

  C#代码编译后可以在相应的目录中找到编译后的 .dll 源文件，如C:\helloworld.dll。

using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
  
public class HelloWorldProc
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void HelloWorld(out string text)
    {
        text = "Hello world!";
    }
}

• 数据库中注册程序集

CREATE ASSEMBLY helloworld from 'C:\helloworld.dll' WITH PERMISSION_SET = SAFE

• 创建存储过程

CREATE PROCEDURE hello
@i nchar(25) OUTPUT
AS
EXTERNAL NAME helloworld.HelloWorldProc.HelloWorld;

helloworld：是上文注册的程序集名称

HelloWorldProc：是C#代码的Class名称

HelloWorld：是C# 代码的方法名称，该方法必须是静态方法

• 执行存储过程

DECLARE @J NCHAR(25);
EXEC hello @J out;
PRINT @J;

执行后输出：Hello world!

八、外部调用存储过程

        除了在数据库中执行指定的存储过程，外部也可以使用代码调用数据库中的存储过程。以下是C#代码调用SQL Server存储过程的例子。

using Microsoft.Data.SqlClient;
using System.Data;

namespace Invoke_SQL_Procedure
{
    internal class Program
    {
        static void Main(string[] args)
        {
            string connectionString = "Server=LMJ\\SQLEXPRESS;Database=Db;Trusted_Connection=True;TrustServerCertificate=true;";  
            string storedProcedureName = "PRO_add";

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                using (SqlCommand command = new SqlCommand(storedProcedureName, connection))
                {
                    command.CommandType = CommandType.StoredProcedure;

                    command.Parameters.AddWithValue("@p1", 10);

                    command.Parameters.AddWithValue("@p2",5);

                    SqlParameter p3 = new SqlParameter("@p3", SqlDbType.Int);
                    p3.Direction = ParameterDirection.Output;
                    command.Parameters.Add(p3);

                    command.ExecuteNonQuery();
                    int result = (int)p3.Value;
                    Console.WriteLine(result);
                }
            }
        }
    }
}

补充：微软官方文档：存储过程（数据库引擎） - SQL Server | Microsoft Learn