Encoding and decoding with Microsoft SQL Server using Postman

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

enter image description here

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

enter image description here

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

enter image description here

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

enter image description here

>Solution :

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.

Leave a Reply