CODEWITHSUNDEEP
sqlsql-server
EaziLuizi
EaziLuizi

Reputation: 1615

Dynamic Sql Convert inserting varbinary data

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

Upvotes: 1

Views: 4514

Answers (2)

valex
valex

Reputation: 24144

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
)
')

Upvotes: 0

Amit Rai Sharma
Amit Rai Sharma

Reputation: 4225

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

Upvotes: 2

Related Questions