Dynamic query and Varbinary

Recently, I came across a strange issue when working with dynamic SQL query along with varbinary data type. I need to insert user’s uploaded image into database.

Because I have multiple varbinary columns to store images, I thought of using single stored procedure to handle insert or updated and that too with single varbinary input parameter along with the intended column.

We build dynamic SQL query and formatted it with the column name as well as binary array (image) content as input parameter. See below:

Set @Query = 'INSERT INTO [dbo].[DashboardImage]( [MemberID], ' + @Field + ')VALUES('+ convert(varchar(max), @MemberID) +','+ cast(@Value as varchar(max)) +')'

Where the input parameter declared as:

@Value varbinary(max),

@Field varchar(200),

When compiled everything was fine. However, when executed with input parameters the query produces type conversion errors like "Cannot convert value from varchar to varbinary" or “Explicit conversion from data type  to varchar is not allowed” or "String or binary data would be truncated."

After going through online resources I got a solution as predefined function “master.dbo.fn_varbintohexstr(@Value)” within Master database which will convert varbinary data type to a varchar or string.

So, here is the new dynamic query which solved our problem

Set @Query =  'INSERT INTO [dbo].[DashboardImage]( [MemberID], ' + @Field + )VALUES('+ convert(varchar(max), @MemberID) +','+ master.dbo.fn_varbintohexstr(@Value) + ')'

And finally to execute the query

EXEC(@Query)

Happy Coding