Create a binary string of zeros, with variable length

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:

  • Rails 3 - Easily work with Pascal Case column names
  • How to check for mutual existence of Fields in same table in Two columns
  • Parsing a string SQL
  • How to detect SQL Server Express in WiX installer
  • Whats the standard way of getting the last insert id?
  • Query executed from Nhibernate is slow, but from ADO.NET is fast
  • 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.

    3 Solutions collect form web for “Create a binary string of zeros, with variable length”

    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))
    
    ; 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 )
    

    The original form

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

    still suits my needs best.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.