使用Postman调用SQL Server编码解码存储过程的异常排查

我编写了用于Web API的编码存储过程：

CREATE PROCEDURE [dbo].[LWP_WEB_ENCODE]
    @order_no int, 
    @dept varchar(2)
    
    DECLARE @customer_no int 
    SET @customer_no = (SELECT customer_no FROM table_order 
                        WHERE order_no = @order_no)
        
    DECLARE @string varchar (4000), @encoded VARBINARY(max)

    SELECT @string = CAST(@order_no AS varchar) + '-' + 
                     CAST(@customer_no AS varchar) + '-' + @dept_abbreviation
            
    SELECT @encoded = CONVERT(VARBINARY(MAX), @string)
    SELECT @encoded AS 'encoded'

直接在SQL中执行exec [LWP_WEB_ENCODE] @order_no = 7267587, @dept_abbreviation = 'S'时，返回的是0x开头的十六进制格式结果；但通过Postman调用Web API时，返回的结果完全不同。

解码存储过程核心代码如下：

SELECT CONVERT(VARCHAR(MAX), @encoded) AS 'decoded'

在SQL中执行exec [LWP_WEB_DECODE] @encoded = 0x373236373538372D38373132323938372D53可以正常解码，但用Postman返回的值调用解码接口时无法正常工作——因为Postman返回的是字符串而非十六进制值，求解决办法。

SQL Server的VARBINARY类型在Web API序列化时，不会自动转成SSMS里显示的0x前缀十六进制文本，而是会被序列化为Base64编码字符串。这就是直接执行和Web调用返回结果不同的核心原因：

• SSMS为了可读性，把VARBINARY显示为0x开头的十六进制格式；
• Web API的序列化组件（比如ASP.NET Core默认的System.Text.Json）会将VARBINARY字节数组转成Base64字符串，比如示例中的0x373236373538372D38373132323938372D53会被转成NzI2NzU4Ny04NzEyMjk4Ny1T。

可以从编码或解码两个方向调整，任选其一即可：

方案1：修改编码存储过程，直接返回Base64字符串 #

让存储过程直接输出Base64格式的字符串，这样Web API返回的内容可以直接传递给解码接口：

CREATE PROCEDURE [dbo].[LWP_WEB_ENCODE]
    @order_no int, 
    @dept_abbreviation varchar(2)
AS
BEGIN
    DECLARE @customer_no int 
    SET @customer_no = (SELECT customer_no FROM table_order 
                        WHERE order_no = @order_no)
        
    DECLARE @string varchar (4000), @encoded VARBINARY(max)

    SELECT @string = CAST(@order_no AS varchar) + '-' + 
                     CAST(@customer_no AS varchar) + '-' + @dept_abbreviation
            
    SELECT @encoded = CONVERT(VARBINARY(MAX), @string)
    -- 直接返回Base64字符串
    SELECT CAST(N'' AS XML).value('xs:base64Binary(sql:variable("@encoded"))', 'VARCHAR(MAX)') AS 'encoded'
END

方案2：修改解码存储过程，支持Base64字符串输入 #

如果不想改编码逻辑，就修改解码存储过程，接收Base64字符串并转成VARBINARY再解码：

CREATE PROCEDURE [dbo].[LWP_WEB_DECODE]
    @encoded_str VARCHAR(MAX) -- 接收Base64字符串
AS
BEGIN
    DECLARE @encoded VARBINARY(MAX)
    -- 将Base64字符串转成VARBINARY
    SET @encoded = CAST(N'' AS XML).value('xs:base64Binary(sql:variable("@encoded_str"))', 'VARBINARY(MAX)')
    -- 原解码逻辑
    SELECT CONVERT(VARCHAR(MAX), @encoded) AS 'decoded'
END

调用时直接传入Postman返回的Base64字符串即可，比如：

exec [LWP_WEB_DECODE] @encoded_str = 'NzI2NzU4Ny04NzEyMjk4Ny1T'

内容的提问来源于stack exchange，提问作者YelizavetaYR