D J Horton Consulting Ltd

Personal reference to useful tips and tricks for SQL Server, SQL Server Reporting Services and Crystal Reports.


My alternative to scraps of paper lying about.








Wednesday, 9 December 2009

Create Common Table Expression of numbers - SQL

To create a Common Table Expression (CTE) of numbers for use in queries:


DECLARE @n AS INT;
SET @n = 100;
WITH Nums AS
(
SELECT 1 AS n
UNION ALL
SELECT
n + 1 FROM Nums WHERE n < @n
)
SELECT * FROM Nums

No comments: