Convert nvarchar(max) to varbinary(max)

Question

Have table with values

report nvarchar(max) not null
description nvarchar(max) 

In stored procedure I want select values from table and then convert it to varbinary max. I seletct :

select 
    CONVERT(varbinary(max), [report]) as [report], 
    ISNULL(CONVERT(varbinary(max), [description]), '') as [description]
from myTbl

but I get an error:

Implicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.

Please help me to solve this problem

Answer 1

The failure is occurring because you convert description to varbinary, but then try to cast any null values back to a varchar. You just need to move ISNULL inside the CONVERT or change the conversion value when null to a binary value.

ISNULL in CONVERT

SELECT 
    CONVERT(varbinary(MAX), report), 
    CONVERT(varbinary(max), ISNULL([description], '')) as [description]
FROM myTbl

Proper ISNULL Value

SELECT 
    CONVERT(varbinary(MAX), report), 
    ISNULL(CONVERT(varbinary(max), [description]), 0x) as [description]
FROM myTbl

Both versions will produce the same output 0x if description is null.