Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Convert nvarchar(max) to varbinary(max)

Tags:

sql

sql-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

like image 585
GeoVIP Avatar asked Dec 16 '22 07:12

GeoVIP


1 Answers

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.

like image 61
Daniel Gimenez Avatar answered Jan 10 '23 09:01

Daniel Gimenez