简书链接:sqlserver存储过程案例模板
文章字数：1072,阅读全文大约需要4分钟

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
CREATE PROCEDURE OrderProcessing
    @OrderID INT
AS
BEGIN
    -- 判断订单是否存在
    IF NOT EXISTS (SELECT * FROM Orders WHERE OrderID = @OrderID)
    BEGIN
        RAISERROR ('Invalid order ID', 16, 1)
        RETURN
    END

    -- 获取订单状态
    DECLARE @Status VARCHAR(20)
    SELECT @Status = Status FROM Orders WHERE OrderID = @OrderID

    -- 根据订单状态执行不同的操作
    IF @Status = 'New'
    BEGIN
        UPDATE Orders SET Status = 'Processing' WHERE OrderID = @OrderID
        EXEC StartOrderProcessing @OrderID
    END
    ELSE IF @Status = 'Processing'
    BEGIN
        DECLARE @Attempts INT
        SET @Attempts = 0

        WHILE @Attempts < 3
        BEGIN
            -- 模拟订单处理过程中断
            WAITFOR DELAY '00:00:10'

            -- 检查订单处理是否完成
            IF EXISTS (SELECT * FROM OrderProcessingLog WHERE OrderID = @OrderID AND CompleteTime IS NOT NULL)
            BEGIN
                UPDATE Orders SET Status = 'Completed' WHERE OrderID = @OrderID
                EXEC NotifyCustomer @OrderID
                BREAK
            END
            ELSE
            BEGIN
                SET @Attempts = @Attempts + 1
            END
        END

        IF @Attempts = 3
        BEGIN
            -- 处理失败，通知客服人员
            EXEC NotifySupport @OrderID
        END
    END
    ELSE IF @Status = 'Completed'
    BEGIN
        RAISERROR ('This order has already been completed', 16, 1)
        RETURN
    END
END

该存储过程的逻辑如下：

首先判断指定的订单是否存在。如果不存在，抛出一个异常并退出存储过程。
获取订单的状态，并根据不同的状态执行不同的操作。
如果订单状态是 New，则将其状态更新为 Processing 并调用名为 StartOrderProcessing 的存储过程，开始处理订单。
如果订单状态是 Processing，则重复执行以下步骤，直到订单处理完成或尝试次数达到 3 次：
等待 10 秒钟（模拟订单处理过程中断）。
检查订单处理是否已经完成。
如果处理完成，则将订单状态更新为 Completed、调用名为 NotifyCustomer 的存储过程通知客户，并退出循环。
如果未完成，则增加尝试次数。
如果尝试次数达到 3 次，则认为处理失败，调用名为 NotifySupport 的存储过程通知客服人员。
如果订单状态是 Completed，则抛出一个异常并退出存储过程。
该存储过程涉及到许多高级语法和逻辑，包括条件判断、循环、异常处理等。通过这个示例，您可以更好地理解 SQL Server 存储过程的复杂性，并了解如何使用它们来管理复杂的业务逻辑。

CalculateEmployeeSalary，它接受三个输入参数 @EmployeeID、@StartDate 和 @EndDate，并计算指定员工在指定时间段内的薪水总额，并将结果作为输出参数返回。该存储过程的逻辑如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE PROCEDURE CalculateEmployeeSalary
    @EmployeeID INT,
    @StartDate DATE,
    @EndDate DATE,
    @TotalSalary MONEY OUTPUT
AS
BEGIN
    DECLARE @Salary MONEY

    -- 计算薪水总额
    SELECT @Salary = SUM(Salary)
    FROM SalaryHistory
    WHERE EmployeeID = @EmployeeID
    AND StartDate >= @StartDate
    AND EndDate <= @EndDate

    -- 设置输出参数
    SET @TotalSalary = @Salary
END

举例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
CREATE PROCEDURE [dbo].[proc_AddOrUpdate_CustomParts]
    @id BIGINT,
    @PartNumber VARCHAR(50),
    @BPartNumber VARCHAR(50),
    @GroupName VARCHAR(50),
    @VendorFill VARCHAR(50),
    @InternalVersion VARCHAR(50),
    @CustomerInternalEngineering VARCHAR(50),
    @UserName VARCHAR(50),
    @ReturnCode INT OUTPUT,
    @ReturnMSG VARCHAR(200) OUTPUT
AS
BEGIN
    DECLARE @NewID INT, @count INT;
    
    -- 初始化返回值
    SELECT @ReturnCode = -1, @ReturnMSG = '失败';

    -- 开始事务处理
    BEGIN TRAN gusn;

    IF (@id > 0)
    BEGIN
        -- 更新现有客制化零件信息
        UPDATE Custom_Parts SET VendorFill = @VendorFill, InternalVersion = @InternalVersion, CustomerInternalEngineering = @CustomerInternalEngineering, WhoUpdate = @UserName, UpdateDate = GETDATE()
        WHERE id = @id;
        
        -- 检查是否更新成功
        IF @@ERROR <> 0
        BEGIN
            -- 如果发生错误，回滚事务并退出
            GOTO ErrTransHandle;
        END

        -- 将旧记录插入到历史表中进行备份
        INSERT INTO Custom_Parts_History SELECT * FROM Custom_Parts WHERE id = @id;
        IF @@ERROR <> 0
        BEGIN
            GOTO ErrTransHandle;
        END

        -- 更新 Custom_Parts_Detail 表中对应的记录
        UPDATE Custom_Parts_Detail SET id = @id WHERE PartNumber = @PartNumber AND BPartNumber = @BPartNumber AND GroupName = @GroupName;
        IF @@ERROR <> 0
        BEGIN
            GOTO ErrTransHandle;
        END

        -- 将旧记录插入到历史表中进行备份
        INSERT INTO Custom_Parts_Detail_History SELECT * FROM Custom_Parts_Detail WHERE id = @id;
        IF @@ERROR <> 0
        BEGIN
            GOTO ErrTransHandle;
        END
    END
    ELSE
    BEGIN
        -- 检查是否已经存在相同的零件分组
        SELECT @count = COUNT(*) FROM Custom_Parts WHERE PartNumber = @PartNumber AND BPartNumber = @BPartNumber AND GroupName = @GroupName;

        IF (@count > 0)
        BEGIN
            -- 如果零件分组已存在，设置返回消息并退出
            SELECT @ReturnMSG = '已维护';
            GOTO ErrTransHandle;
        END

        -- 添加新的客制化零件信息
        INSERT INTO Custom_Parts (PartNumber, BPartNumber, GroupName, VendorFill, InternalVersion, CustomerInternalEngineering, WhoRec) VALUES 
                                 (@PartNumber, @BPartNumber, @GroupName, @VendorFill, @InternalVersion, @CustomerInternalEngineering, @UserName);
        IF @@ERROR <> 0
        BEGIN
            GOTO ErrTransHandle;
        END

        SET @NewID = @@IDENTITY;

        -- 将新记录插入到历史表中进行备份
        INSERT INTO Custom_Parts_History SELECT * FROM Custom_Parts WHERE id = @NewID;
        IF @@ERROR <> 0
        BEGIN
            GOTO ErrTransHandle;
        END

        -- 更新 Custom_Parts_Detail 表中对应的记录
        UPDATE Custom_Parts_Detail SET id = @NewID WHERE PartNumber = @PartNumber AND BPartNumber = @BPartNumber AND GroupName = @GroupName;
        IF @@ERROR <> 0
        BEGIN
            GOTO ErrTransHandle;
        END

        -- 将新记录插入到历史表中进行备份
        INSERT INTO Custom_Parts_Detail_History SELECT * FROM Custom_Parts_Detail WHERE id = @NewID;
        IF @@ERROR <> 0
        BEGIN
            GOTO ErrTransHandle;
        END
    END
    
    -- 设置返回值并提交事务
    SELECT @ReturnCode = 0, @ReturnMSG = '成功';
    COMMIT TRAN gusn;
    RETURN @ReturnCode;

ErrHandle:
    RETURN @ReturnCode;

ErrTransHandle:
    -- 回滚事务并设置返回值
    ROLLBACK TRAN gusn;
    RETURN @ReturnCode;
END;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
IF OBJECT_ID('dbo.MyStoredProcedure', 'P') IS NOT NULL
    DROP PROCEDURE dbo.MyStoredProcedure
GO

CREATE PROCEDURE dbo.MyStoredProcedure
    @inputString VARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        BEGIN TRANSACTION
        
        DECLARE @tempTable TABLE (id INT, name VARCHAR(50), age INT)
        
        -- 将输入字符串拆分为临时表
        INSERT INTO @tempTable
        SELECT 
            CAST(LEFT(item, CHARINDEX(',', item) - 1) AS INT), 
            SUBSTRING(item, CHARINDEX(',', item) + 1, LEN(item)), 
            NULL
        FROM 
            STRING_SPLIT(@inputString, ';')
            
        -- 检查最后一条 SQL 语句是否有错误
        IF @@ERROR != 0
            GOTO ErrorHandler; 
        
        -- 根据姓名匹配更新年龄列
        UPDATE 
            t
        SET 
            t.age = c.age
        FROM 
            @tempTable t
        INNER JOIN 
            dbo.Customers c ON t.name = c.name
        
        -- 检查最后一条 SQL 语句是否有错误
        IF @@ERROR != 0
            GOTO ErrorHandler; 
        
        -- 如果没有更新行，则引发错误
        IF @@ROWCOUNT = 0
        BEGIN
            RAISERROR('没有更新行。', 16, 1)
            GOTO ErrorHandler;
        END
        
        -- 将年龄转换为字符串，并使用 CASE WHEN 语句设置消息
        DECLARE @ageMessage VARCHAR(100)
        SET @ageMessage = 
            CASE 
                WHEN MAX(t.age) < 18 THEN '年轻人'
                WHEN MAX(t.age) >= 18 AND MAX(t.age) <= 65 THEN '成年人'
                ELSE '老年人'
            END
        
        -- 打印带有年龄类别的消息
        PRINT '列表中最年长的人是：' + @ageMessage
        
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION
        
        -- 打印错误消息及其详细信息
        PRINT '错误：' + ERROR_MESSAGE() + '，行：' + CAST(ERROR_LINE() AS VARCHAR(10))
        
        -- 返回错误代码
        RETURN @@ERROR;
    END CATCH
    
    -- 如果没有出现错误，则返回 0
    RETURN 0;

ErrorHandler:
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
    
    -- 返回错误代码
    RETURN @@ERROR;
END
GO

EXEC dbo.MyStoredProcedure ‘1,John;2,Jane;3,Bob;’

在这个例子中，假设 Customers 表中有如下数据：

id name age
1 John 24
2 Jane 33
3 Alice 12
4 Bob 47
因此，存储过程将打印出以下消息：

The oldest person in the list is a(n) Adult

高级语法之发起http业务

CHARINDEX('{', @responseText) 
1
2
3
``` @access_token = JSON_VALUE(@JSON, '$.access_token');```
从json字符串解析键access_token
,内容为```{ "accesstoken":"xxxxxxxxxx"}```

ALTER PROCEDURE [dbo].[SendMessageToWechat]
@corp_id varchar(50),
@secret varchar(50),
@touser varchar(50),
@agent_id varchar(50),
@content varchar(500),
@returnResult varchar(8000) output,
@returnCode int output,
@returnMsg varchar(8000) output
AS
BEGIN

DECLARE @Object INT;
DECLARE @Url VARCHAR(2000);
DECLARE @Result VARCHAR(MAX);
DECLARE @access_token VARCHAR(800);
declare @errmsg varchar(500);
DECLARE @POST VARCHAR(MAX);
SET @Url = 'https://qyapi.weixin.qq.com/cgi-bin/gettoken?corpid=' + @corp_id + '&corpsecret=' + @secret;

DECLARE @return_value int,
@responseText varchar(8000),
@responseStatus varchar(800),
@responseCode varchar(800)
set @responseCode=’900’
EXEC @return_value = [dbo].[SP_HTTPRequest]
@URI = @Url,
@methodName = N’get’,
@responseText = @responseText OUTPUT,
@responseStatus = @responseStatus OUTPUT,
@responseCode = @responseCode OUTPUT
IF @responseCode<>’200’
BEGIN
set @returnResult=@responseText;
set @returnCode=CAST(@responseCode AS INT);
set @returnMsg=’GetTokenFail,HttpResponseError StatusCode is‘+@responseCode;
RETURN;
END
IF CHARINDEX(‘{‘, @responseText) = 0
BEGIN
set @returnResult=@responseText;
set @returnCode=800;
set @returnMsg=’GetTokenFail,ParseJSONFormatError ,Content Format Error’;
RETURN;
END

DECLARE @JSON NVARCHAR(MAX) = @responseText;
DECLARE @errcode INT = JSON_VALUE(@JSON, '$.errcode');

IF @errcode <> 0
BEGIN
    set @returnResult=@responseText;
    set @returnCode=800;
    set @returnMsg='GetTokenFail,pusherrcode is '+@errcode;
  
    RETURN;
END

set @access_token = JSON_VALUE(@JSON, '$.access_token');

SET @POST = '{
    "touser": "' + @touser + '",
    "agentid": ' + @agent_id + ',
    "is_to_all": true,
    "msgtype": "text",
    "text": {
        "content": "' + @content + '"
    }
}';

SET @Url = 'https://qyapi.weixin.qq.com/cgi-bin/message/send?access_token='+ @access_token;
EXEC    @return_value = [dbo].[SP_HTTPRequest]
    @URI = @Url,
    @methodName = N'post',
    @requestBody = @POST, 
    @responseText = @responseText OUTPUT,
    @responseStatus = @responseStatus OUTPUT,
    @responseCode = @responseCode OUTPUT
IF @responseCode<>'200'
BEGIN
    set @returnResult=@responseText;
    set @returnCode=CAST(@responseCode AS int);
    set @returnMsg='SendFail,HttpResponseError  StatusCode is'+@responseCode;
    RETURN;
END
IF  CHARINDEX('{', @responseText) = 0
BEGIN
    set @returnResult=@responseText;
    set @returnCode=800;
    set @returnMsg='SendFail,ParseJSONFormatError ,Content Format Error';
    RETURN;
END
 set @JSON = @responseText;
 --select JSON_VALUE(@JSON, '$.errcode')
set  @errcode  =CAST(JSON_VALUE(@JSON, '$.errcode') as int);

set  @errmsg  =JSON_VALUE(@JSON, '$.errmsg');
IF @errcode <> 0
BEGIN
    set @returnResult=@responseText;
    set @returnCode=800;
    set @returnMsg='PushFail,Pusherrcode is '+cast(@errcode as varchar(100))+ ' '+@errmsg --+' token is '+@access_token;
    RETURN;
END
set @returnResult=@responseText;
set @returnCode=200;
set @returnMsg='PushSuccess';

END