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.
The original form
CAST(REPLICATE(CAST(CAST(0 AS tinyint) AS varbinary(max)), @size)
AS varbinary(max))
still suits my needs best.
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))
SPACE won't generate more than 8000 chars...; 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 )
