CODEWITHSUNDEEP
stored-proceduresparameterssql-server-2008-r2varbinary
BillP3rd
BillP3rd

Reputation: 1019

Passing VARBINARY to stored procedure

I have some source data that is formatted as ASCII hexadecimal. I need to get it into a SQL database in VARBINARY fields. I've reduced the problem to the bare minimum to illustrate what I'd like to do. I have a stored procedure:

CREATE PROCEDURE BinaryDemo
    @BinaryData varbinary(max)
AS
BEGIN
    PRINT @BinaryData;
END

I know that it "works" because I can do:

DECLARE @tmp varbinary(max);
SET @tmp = CONVERT(varbinary, '1234567890abcdef', 2);
EXEC BinaryDemo @BinaryData=@tmp;

What I'd like to do is skip the intermediate steps and invoke the procedure like:

EXEC BinaryDemo @BinaryData=CONVERT(varbinary, '1234567890abcdef', 2);

Unfortunately, SQL complains about the syntax: Incorrect syntax near the keyword 'CONVERT'.

I know that the CONVERT is correct because I can:

PRINT CONVERT(varbinary, '1234567890abcdef', 2);

and I see exactly what I expect. The first example (declare/set/exec) is really a poor option because of the nature and quantity of the source data.

Upvotes: 6

Views: 12088

Answers (1)

Royi Namir
Royi Namir

Reputation: 148524

nice question.

but i thont think its possible . its like :

works good : 

  DECLARE @r DATETIME
    SET @r=GETDATE()

    EXEC     [dbo].[sp_myDatePrinter] @d=@r

bad :

DECLARE @r DATETIME
    EXEC     [dbo].[sp_myDatePrinter] @d=GETDATE();

Upvotes: 1

Related Questions