1

I have a variable that is a hex with reversed bytes. To reverse bytes I found the solution with type binary and reversing it depends on its size. Since I have to reverse bytes of variable @card which can be different sizes I was thinking of declaring variable @binarylen, type int, which I want to set as data type length but get an error 'incorrect syntax: expecting ID, INTEGER or QOUTED_ID'. Can I somehow do this?

DECLARE @card_hex nvarchar(max)
DECLARE @card_int bigint
DECLARE @badgenr nvarchar(max)
set @badgenr= '120224205061286445' 

-- convert it to hex
        set @card_int = convert (bigint, (@badgenr))
        print @card_int
        set @card_hex = CONVERT(NVARCHAR(31),CONVERT(VARBINARY, @card_int),2)
        print @card_hex

--remove leading zeroes:
set @card_hex = (select substring(@card_hex, patindex('%[^0]%',@card_hex), 20))
print @card_hex

DECLARE @bitelen as int
set @bitelen= LEN(@card_hex)

declare @binarylen as int
set @binarylen=@bitelen/2 + @bitelen%2

declare @reverse as binary(@binarylen)
set @reverse=( SELECT cast(reverse(cast(@card_int AS BINARY(@binarylen))) AS BINARY(@binarylen)))
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user6845132
  • 19
  • 2
  • 4
    Since it's `varbinary` simply use some length that is a bit more than the maximum length you expect. – Zohar Peled Aug 25 '20 at 07:58
  • 1
    To answer the question, no you can't use a variable to define a variable or in fact anything that needs to be defined using a literal. – Thom A Aug 25 '20 at 08:08
  • 1
    All types in T-SQL have pre-defined (maximum) sizes, even if the value contained within them can be of variable length. Only the built-in functions can return different types based on their input type, an ability which is not available to user-defined functions. The only concession T-SQL makes to "flexible" typing is `SQL_VARIANT`, which however is largely useless as it requires you to already know the correct type in it before you can effectively use it, or else get funky with dynamic SQL. – Jeroen Mostert Aug 25 '20 at 09:33
  • I tried to declare @binarylen as SQL_VARIANT because I know it has to be type INT (you can see it in the code above) but I have the error saying I'm using wrong syntax because I can't declare a variable as binary(select cast(@binarylen as int)) – user6845132 Aug 25 '20 at 12:11
  • I only mentioned `SQL_VARIANT` for completeness. You cannot solve your problem with it. There is no T-SQL syntax for creating a value of a type with a specific length at runtime (except for dynamic SQL, which is not accessible in functions). You can always just use `VARBINARY(MAX)` and accept that the code using it will have to take care of casting it to something more specific if required. Speaking of which -- [do not use `VARBINARY` without a length](https://sqlblog.org/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length) (the link talks about `NVARCHAR` but it's the same thing). – Jeroen Mostert Aug 25 '20 at 12:17
  • It seems that in @badgenr you have the digits of a number that fits in a bigint. In this case you can use binary(8), because bigint has 8 bytes. – Razvan Socol Aug 30 '20 at 17:54

0 Answers0