Tuesday, 25 July 2017

MSSQL - Function to generate empty rows

There the text of inline table-value function to generate a specified count of empty rows:

  1. IF OBJECT_ID(N'dbo.GenerateRows') IS NOT NULL
  2. DROP FUNCTION dbo.GenerateRows;
  3. GO
  4. CREATE FUNCTION dbo.GenerateRows (@Count INT)
  5. /*
  6. https://yabele.blogspot.com/2017/07/mssql-function-to-generate-empty-rows.html
  7. */
  8. RETURNS TABLE
  9. AS
  10. RETURN
  11. WITH TenRows AS (
  12. SELECT * FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS T(I)
  13. )
  14. SELECT TOP(@Count)
  15. I = ROW_NUMBER() OVER(ORDER BY (SELECT 1))
  16. FROM
  17. TenRows T1 CROSS JOIN TenRows T2 CROSS JOIN TenRows T3 CROSS JOIN TenRows T4 CROSS JOIN TenRows T5
  18. /*
  19. SELECT * FROM dbo.GenerateRows(100);
  20. */
  21. ;

No comments:

Post a Comment

Note: only a member of this blog may post a comment.