CREATE FUNCTION with a WHILE LOOP

Question

CREATE FUNCTION with a WHILE LOOP

patelxx

SSC Eights!

Points: 853
More actions
December 1, 2016 at 10:35 am

#337867

I am learning and new to CREATE FUNCTIONS - how to I add a WHILE LOOP in a function:
CREATE FUNCTION fn_list_the_number(@count INT)
RETURNS TABLE
AS
RETURN
(
WHILE (@count <= 10)
BEGIN
PRINT CAST(@count AS NVARCHAR);
SET @count = @count + 1;
END
)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

patelxx (12/1/2016)
I am learning and new to CREATE FUNCTIONS - how to I add a WHILE LOOP in a function:
CREATE FUNCTION fn_list_the_number(@count INT)
RETURNS TABLE
AS
RETURN
(
WHILE (@count <= 10)
BEGIN
PRINT CAST(@count AS NVARCHAR);
SET @count = @count + 1;
END
)

Your code isn't returning a table. Read Books Online and you will see that inline TABLE valued function must contain just a SELECT statement.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

You should also read this article to avoid looping in SQL.

http://dwaincsql.com/2014/03/27/tally-tables-in-t-sql/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 3

In SQL Server functions always return some kind of value, whether that's a single value or table, there's isn't a concept of void functions in SQL Server that just do something but don't return anything, that would be a stored procedure.

Alan Burstein SSC Guru Points: 61136 More actions · Answer 4

patelxx (12/1/2016)
I am learning and new to CREATE FUNCTIONS - how to I add a WHILE LOOP in a function:
CREATE FUNCTION fn_list_the_number(@count INT)
RETURNS TABLE
AS
RETURN
(
WHILE (@count <= 10)
BEGIN
PRINT CAST(@count AS NVARCHAR);
SET @count = @count + 1;
END
)

There are 3 kinds of user-defined T-SQL functions in SQL Server: scalar-valued, multi-statement table valued, and inline table-valued functions. We'll ignore scalar for for now and look at the two major differences between multi-statement (mTVF) and inline table valued functions (iTVF):

1. mTVFs are almost always slow (very) whereas iTVFs can be extremely fast

2. mTVFs allow you to create traditional while loops where iTVFs do not.

You are trying to create while loop inside of an iTVF which is not allowed. You have two options:

1. Create an mTVF and create your while loop there; this will be slow.

2. Create an iTVF and use a tally table instead of a loop (note the article Luis included a link to); this will be very fast.

Learning tally tables can be a little tricky but will help you earn a reputation as the person who writes really, really fast queries. Here's how you would do what (I think) you are trying to do using an mTVF:

CREATE FUNCTION dbo.fn_list_the_number(@count int)

RETURNS @numbers TABLE (N int)

AS

BEGIN

DECLARE @i int = 1; -- my "iterator"

WHILE @i <= @count

BEGIN

INSERT @numbers VALUES (@i);

SET @i = @i+1

END

RETURN;

END

Now, let's do the same thing using a tally table and an iTVF:

CREATE FUNCTION dbo.itvf_list_the_number (@count int)

RETURNS TABLE AS

RETURN

(

WITH E(e) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t(x))

SELECT TOP (@count) N = ROW_NUMBER() OVER (ORDER BY (SELECT 1))

FROM E a, E b, E c, E d -- update to 10K numbers but you can add more

);

This code may look bizarre but is profoundly more efficient. Both functions do the same thing, the second just does it much faster:

SELECT * FROM dbo.fn_list_the_number(10);

SELECT * FROM dbo.itvf_list_the_number(10);

Here's a little more about Tally Tables: The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url]

Edit: post bogus link, typos

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

patelxx SSC Eights! Points: 853 More actions · Answer 5

Thank you guys for your response.

Alan loved your example.