SQL Server: how to create zero table with certain dimensions n and m?

Question

Suppose you want n rows and m columns. Zero table is a table with all values equal to zero. You want to create an zero table of the dimensions n times m. How can you create a zero table with dimensions n and m in SQL Server 2014?

Answer 1

Dynamically creating a table of 0s:

declare @n int = 10; /* rows */
declare @m int = 10; /* columns */

declare @cols nvarchar(max);
declare @sql  nvarchar(max);

select @cols = stuff((
    select top (@m) ',' + quotename(n) + ' = 0'
    from (
      select n=row_number() over (order by (select 1)) 
      from master..spt_values
      ) t
    order by n
    for xml path (''), type).value('.','nvarchar(max)')
  ,1,1,'');

select @sql = '
select ' + @cols + '
from (
  select top (@n) n=row_number() over (order by (select 1))
  from master..spt_values
  ) t
';

select @sql as CodeGenerated;
exec sp_executesql @sql, N'@n int', @n;

rextester demo: http://rextester.com/PKLUA61633

returns:

+-----------------------------------------------------------------------------------------+
|                                      CodeGenerated                                      |
+-----------------------------------------------------------------------------------------+
| select [1] = 0,[2] = 0,[3] = 0,[4] = 0,[5] = 0,[6] = 0,[7] = 0,[8] = 0,[9] = 0,[10] = 0 |
| from (                                                                                  |
|   select top (@n) n=row_number() over (order by (select 1))                             |
|   from master..spt_values                                                               |
|   ) t                                                                                   |
+-----------------------------------------------------------------------------------------+

and the exec sp_executesql returns:

+---+---+---+---+---+---+---+---+---+----+
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
+---+---+---+---+---+---+---+---+---+----+
| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |  0 |
| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |  0 |
| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |  0 |
| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |  0 |
| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |  0 |
| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |  0 |
| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |  0 |
| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |  0 |
| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |  0 |
| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |  0 |
+---+---+---+---+---+---+---+---+---+----+