CODEWITHSUNDEEP
sql-servert-sql
user1016327
user1016327

Reputation: 31

What's with this SQL 'With' clause?

This SQL function below can be used to generate numbers between the low and high. I sort of understand how it is working, but I can't wrap my head around a couple things. First the WITH Clause. This doesn't follow the CTE construct. It's not a CTE, right? What is this syntax?

The L0-l5 and Nums are all tables but I don't understand the 'L0 as' syntax. You can't declare a table that right way,right? Can I do that outside of a function?

ALTER FUNCTION [dbo].[GetNums](@low AS BIGINT, @high AS BIGINT) RETURNS TABLE
    AS
    RETURN
    WITH
        L0   AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) AS D(c)),
        L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
        L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
        L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
        L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
        L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
        Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum FROM L5)
        SELECT TOP(@high - @low + 1) @low + rownum - 1 AS n  FROM Nums  ORDER BY rownum;

Upvotes: 0

Views: 72

Answers (2)

smoore4
smoore4

Reputation: 4866

You might consider a whole different approach too:

DECLARE @i integer = 0
DECLARE @l integer = 0
DECLARE @h integer = 100
DECLARE @output integer
DECLARE @mytable table (m_output int)

WHILE (@i >= @l AND @i < @h)
BEGIN
SET @output = @i
INSERT INTO @mytable(m_output) VALUES(@output);
SET @i = @i + 1;
END

SELECT * FROM @mytable;

Upvotes: 0

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

Read about CTEs. They are defined with a WITH statement and their definitions are comma separated.

TechNet

WITH expression_name [ ( column_name [,...n] ) ]

AS

( CTE_query_definition )

Upvotes: 1

Related Questions