在SQL Server中存储程序状态变量的最佳方法

CREATE PROCEDURE ExampleProcedure
AS
BEGIN
    DECLARE @StateVariable INT = 0;
    -- 使用 @StateVariable 进行操作
END;

-- 示例：使用系统全局变量
SELECT @@VERSION AS Version;

CREATE PROCEDURE ExampleProcedure
AS
BEGIN
    DECLARE @StateTable TABLE (ID INT, Value NVARCHAR(50));
    -- 插入数据到 @StateTable
    INSERT INTO @StateTable (ID, Value) VALUES (1, 'State1');
    -- 查询 @StateTable
    SELECT * FROM @StateTable;
END;

CREATE PROCEDURE ExampleProcedure
AS
BEGIN
    CREATE TABLE #StateTable (ID INT, Value NVARCHAR(50));
    -- 插入数据到 #StateTable
    INSERT INTO #StateTable (ID, Value) VALUES (1, 'State1');
    -- 查询 #StateTable
    SELECT * FROM #StateTable;
    -- 删除临时表
    DROP TABLE #StateTable;
END;

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    [SqlProcedure]
    public static void ExampleCLRProcedure()
    {
        SqlContext.Pipe.Send("State Variable Value");
    }
}

-- 设置Session Context
EXEC sp_set_session_context @key = N'MyKey', @value = 'MyValue';

-- 获取Session Context
SELECT CAST(SESSION_CONTEXT(N'MyKey') AS NVARCHAR(50)) AS SessionValue;