December 1, 2016 at 10:35 am
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
)
December 1, 2016 at 10:44 am
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
December 1, 2016 at 11:01 am
You should also read this article to avoid looping in SQL.
December 1, 2016 at 11:27 am
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.
December 1, 2016 at 12:01 pm
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
-- Itzik Ben-Gan 2001
December 2, 2016 at 3:57 am
Thank you guys for your response.
Alan loved your example.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply