Sql – Convert nvarchar(max) to varbinary(max)

sqlsql server

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

Best Answer

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.

Related Topic