5

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
BillP3rd
  • 1,009
  • 1
  • 9
  • 21
  • Which **version** of SQL Server are you using?? – marc_s Nov 21 '11 at 19:28
  • possible duplicate of [Cast integer and concatenate to varchar in TSQL](http://stackoverflow.com/questions/4936180/cast-integer-and-concatenate-to-varchar-in-tsql) – Martin Smith Nov 21 '11 at 21:22
  • @Martin Smith: I didn't see that particular question (and, yes, I searched). Thanks for pointing it out. – BillP3rd Nov 21 '11 at 21:30

1 Answers1

1

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();
Royi Namir
  • 144,742
  • 138
  • 468
  • 792