Create a binary string of zeros, with variable len

Question

What is the most elegant way to create a string of binary zeros, of type varbinary(max), if the length is specified at run time (e.g., in a stored procedure)?

I could do it using the REPLICATE function, but this involves a lot of casting:

CAST(REPLICATE(CAST(CAST(0 AS tinyint) AS varbinary(max)), @size)
    AS varbinary(max))

(It doesn't even fit on the line...) Is there a better way to do this?

EDIT: The code should work for @size > 8000.

Answer 1

; with Foo as (
  select 1 as Size
  union all
  select Size * 2
    from Foo
    where Size < 65536 )
  select Size, Cast( Replicate( Char( 0 ), Size ) as VarBinary(MAX) ) as WideZero
    from Foo
    option ( maxrecursion 0 )

Answer 2

The original form

CAST(REPLICATE(CAST(CAST(0 AS tinyint) AS varbinary(max)), @size)
    AS varbinary(max))

still suits my needs best.

Answer 3

Better or just shorter? :)

declare @size int
set @size = 3


select  CAST(REPLICATE(CAST(CAST(0 AS tinyint) AS varbinary(max)), @size) AS varbinary(max)),
        cast(replace(space(@size), ' ', 0x0) as varbinary(max))