SQL varbinary IP compare -

i have sql table stores ips varbinary(16). '10.240.200.9' stored 0x0af0c809.

i'm writing stored procedure has create dynamic sql due nature of input variables. 1 of input variables ip address.

let's take mentioned ip , hex. when run query below, gives me following error

the data types varchar , varbinary incompatible in add operator.

i understand it's happening due @hex being concatenated nvarchar string.

i'm trying make sql work

set @sql = 'select * [table] ip = ' + [hexvalue]

you can explicitely cast value compatible type such varchar.

select cast(cast('test' varbinary(4)) varchar(4)) + 'test'

with example:

set @sql = 'select * [table] ip = ''' + cast([hexvalue] varchar(16)) + ''''

Test