0

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?

8
  • CREATE FUNCTION Commented Oct 10, 2019 at 7:31
  • It might be better to share the problem you're trying to solve. Assuming you can produce a table function running a while loop I suspect the performance of the results will be less than stellar. There may well be better set based approaches that can be taken. Commented Oct 10, 2019 at 7:34
  • 4
    Inline TVF can only contain a single SELECT statement. WHILE loops are only possible in multi statement (non inline) TVF Commented Oct 10, 2019 at 7:35
  • I'm want to ask if there is a way to declare variable in inline table-function. Because i'm want to use a while loop with a variable type int and increasing Commented Oct 10, 2019 at 7:36
  • Thanks Martin, may be that is the answer I need Commented Oct 10, 2019 at 7:37

1 Answer 1

1

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.

Sign up to request clarification or add additional context in comments.

5 Comments

Okay, I got the case. But one more question, can I put the parameter in it?
Yes, @HaiNguyen . This was a simplified example. See my closing statement.
Okay, so I need to ask one more question: I have an inline table-value function with a parameter type int. Can I use tally in another function to query the result of the function I have?
That question is too vague to answer, @HaiNguyen. You should be asking a new question, showing what youn are really after; like i have said above a couple a times.
Okay, let me ask new question.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.