Dynamic Sql Convert inserting varbinary data

Question

I have been stuck on a dynamic sql issue. I need to be able to save varbinary (byte[]) data into my table.

Here is sample code just to show my problem:

ALTER PROCEDURE ins_photo
@schema VARCHAR(63), @name VARCHAR(50), @data VARBINARY(MAX)
AS

PRINT @data;

EXEC ('

PRINT '''+@data+''';

DECLARE @plswork VARBINARY(MAX) = CAST('''+@data+''' AS VARBINARY(MAX))

PRINT @plswork;

INSERT INTO '+@schema+'.Tester
(name, photo)
VALUES
(
'''+@name+''',
@plswork
)
')

And here is the output:

0xFFD8FFE000104A4649460001010000
?????A
0x3F3F3F3F3F410100

(1 row(s) affected)

As you will notice in the conversion the initial binary data and the "converted" are different, therefor when retrieving the binary data to display an image ... well reveals nothing :)

I would really appreciate help or even a work around, but the main thing is that this usp will need to call different tables dependent on schema. (Hence why i am using dynamic sql)

Could i save the images as nvarchar(max) and on the client side use ToBase64String() ?, i wouldve thought this is a bad solution but not too sure

Thanking in advance Marco

Amit Rai Sharma · Accepted Answer · 2013-05-07 08:50:52Z

2

I am not sure why you facing this issue. But instead of concatenating the @data to dynamic sql string, try passing it as a parameter. Refer the example mentioned below.

DECLARE @Color varchar(16) = 'Blue', @LastProduct varchar(64)
SET @SQL =       N'SELECT @pLastProduct = max(Name)
                   FROM AdventureWorks2008.Production.Product
                   WHERE Color = @pColor'
SET @ParmDefinition = N'@pColor varchar(16),
                        @pLastProduct varchar(64) OUTPUT'
EXECUTE sp_executeSQL
            @SQL,
            @ParmDefinition,
            @pColor = @Color,
            @pLastProduct OUTPUT

edited May 7, 2013 at 8:50

answered May 7, 2013 at 8:43

Amit Rai Sharma

4,2351 gold badge30 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

valex · Accepted Answer · 2013-05-07 08:55:41Z

0

Try to send a VARCHAR variable instead VARBINARY into EXEC query string.

Add

DECLARE @dataCHAR VARCHAR(MAX);
SET @dataCHAR = @data;

So procedure will look like:

ALTER PROCEDURE ins_photo
@schema VARCHAR(63), @name VARCHAR(50), @data VARBINARY(MAX)
AS

PRINT @data;

DECLARE @dataCHAR VARCHAR(MAX);
SET @dataCHAR = @data;

EXEC ('

PRINT '''+@dataCHAR+''';

DECLARE @plswork VARBINARY(MAX) = CAST('''+@dataCHAR+''' AS VARBINARY(MAX))

PRINT @plswork;

INSERT INTO '+@schema+'.Tester
(name, photo)
VALUES
(
'''+@name+''',
@plswork
)
')

answered May 7, 2013 at 8:55

valex

24.2k7 gold badges47 silver badges62 bronze badges

2 Comments

EaziLuizi Over a year ago

Thanks for the response it does work with a small (very small) image, larger images it fails... hmmmm

EaziLuizi Over a year ago

For the problem i presented your solution was correct, so thank you very much, on implementation though i use large images, so the other answer is "more" correct.. thank you again though much appreciated

Collectives™ on Stack Overflow

Dynamic Sql Convert inserting varbinary data

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related