Encoding and decoding with Microsoft SQL Server using Postman-CodePudding

I wrote the following script to be used through 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'

When I execute the query with the following paramters:

exec [LWP_WEB_LWP_WEB_ENCODE] @order_no = 7267587, @dept_abbreviation = 'S'

I get the following string back

I then do the same thing through postman in an effort to simulate the web calls and get something completely different

I'm not sure why its behaving like this - but also I have a script that decodes the string in the opposite direction and this particular value doesn't work

The decoded procedure basically just runs this

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

When I run my script

exec [LWP_WEB_DECODE] @encoded = 0x373236373538372D38373132323938372D53

I get this in SQL

However then I try to run the value returned in postman it hates it For some reason its not encoding it as I do in SQL and its returning a string and not a hex value

CodePudding user response：

The stored procedure returns binary data. SSMS displays binary data by encoding it in a hex string. JSON and XML can't directly contain binary data, but most often use Base64 to encode it as a string rather than using a hex string.