CODEWITHSUNDEEP
sqlsql-server
Hai Nguyen
Hai Nguyen

Reputation: 13

Variable in inline table-value function

I'm trying to write an inline table-value function using SSMS v18.2 against SQL Server 2017 with a while loop.

Can I have a way to declare a variable in an inline table-value function for a loop?

If it a yes, what is the syntax for this?

Upvotes: 0

Views: 275

Answers (1)

Thom A
Thom A

Reputation: 95544

I'm answering the comment not the question here, which is "Because I'm want to use a while loop with a variable type int and increasing". Simply, put don't. The best (and i do mean best) way to do this is with a Tally (they are faster than a rCTE as well).

The below will generate the number 1-1,000,000, and will do it in a matter of seconds:

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1, N N2, N N3, N N4, N N5) --1 Million
SELECT I
FROM Tally;

The above is a set based solution, and can be "easily" expanded to work against datasets; for example creating all the dates between 2 dates.

You can also read up on Tallys in this article by Jeff Moden: The "Numbers" or "Tally" Table: What it is and how it replaces a loop

This question doesn't really answer the real problem the OP has, it just introduces them to tally 9and this would not fit in a comment). I would suggest, Hai Nguyen, you ask a new question, explaining your real goals, so that we can help you understand how you can put the above into practice.

Upvotes: 1

Related Questions