June 6, 2013 at 5:43 am
Hi, I would like some help with my first inline table valued function,
I have to create one that builds a table containing two columns, one with an integer value that has been derived through a loop, eg a list of integers from one to four and the other needs to have some text based on the integer in the previous column, so maybe odd next to the odd integers and even next to the even ones.
Would anyone be able to point me in the right direction, I have already come acrcoss a problem whereby the declaration of the parameter within the function is not allowed.
I would need to generate the numbers and perform some check to generate the text based on the number (that's my initial approach)
Would you be able to point me in the right direction and guide my approach to the problem?
Much appreciated
Soulchyld
June 6, 2013 at 6:02 am
Did you check out this blog: http://www.sqlservercentral.com/blogs/discussionofsqlserver/2012/02/15/comparing-inline-and-multistatement-table-valued-functions/? According to BOL, an inline function is a single statement, if you need to create variables then it sounds like you might need a multistatement function. If that doesn't work for you, have you thought of trying out a Common Table Expression so you can "loop" through your data.
June 6, 2013 at 6:10 am
This simple sample should get you started. Have a play, explain if it doesn't meet your requirements.
CREATE FUNCTION [dbo].[MyFirstITVF]
(
@StartNum INT
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
SELECT d.MyInt, x.MyString
FROM
(SELECT MyInt = @StartNum + 1 UNION ALL
SELECT MyInt = @StartNum + 2 UNION ALL
SELECT MyInt = @StartNum + 3 UNION ALL
SELECT MyInt = @StartNum + 4) d
CROSS APPLY (SELECT MyString = CASE WHEN d.MyInt%2 = 1 THEN 'Odd' ELSE 'Even' END) x
GO
SELECT * FROM [dbo].[MyFirstITVF] (0)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 6, 2013 at 9:17 am
Ok, That helps a little, I thought I would be able to figure out the rest but alas, what I need is a counter to add the integers until we reach the input number, so if we run the function with 4 it will give us 1,2,3,4 and the text next to it, that is where I think a loop would be required? where would I include my loop in the function?
June 6, 2013 at 9:37 am
Thanks, so the idea is to build a tally table on the fly and select from that for my query is that correct?
June 6, 2013 at 11:16 am
I've heard the tally table described as the "Swiss Army Knife of SQL" by more than one person on this site. It can solve a lot of problems and avoid the performance degradation of looping constructs. The performance gains you can realize are through the roof. It also has the coolness factor.
Yes, you can build one on the fly, but a lot of people keep a tally table as a permanent table. See Jeff Moden's article Luis referenced above for building one.
June 6, 2013 at 12:23 pm
Ed Wagner (6/6/2013)
I've heard the tally table described as the "Swiss Army Knife of SQL" by more than one person on this site. It can solve a lot of problems and avoid the performance degradation of looping constructs. The performance gains you can realize are through the roof. It also has the coolness factor.Yes, you can build one on the fly, but a lot of people keep a tally table as a permanent table. See Jeff Moden's article Luis referenced above for building one.
LOL I just posted this exact thing on another thread. In lieu of using a permanent table I use a view that creates the tally table on the fly. 0 reads to access it and I don't have to remember the syntax over and over.
create View Tally as
WITH
E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
select N from cteTally
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 6, 2013 at 12:27 pm
Now that's a pretty cool idea I hadn't even considered. What's the performance like when compared to a physical table? I haven't tried it, but I'd imagine that, since everything's done in memory, you'd completely avoid the cost of disk I/O.
June 6, 2013 at 12:43 pm
Ed Wagner (6/6/2013)
Now that's a pretty cool idea I hadn't even considered. What's the performance like when compared to a physical table? I haven't tried it, but I'd imagine that, since everything's done in memory, you'd completely avoid the cost of disk I/O.
This discussion is almost an exact duplicate from the other thread.
As long as the definition is memory there is 0 reads. If it has to load the definition, it is still faster than a physical read from a table. Super duper fast.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 6, 2013 at 12:45 pm
Excellent - thanks very much for the tip. I'm going to have to try this one.
June 6, 2013 at 2:27 pm
Thanks everyone, I eventually managed to solve it and will stick the code up sometime soon, Hopefully it will be acceptable
June 7, 2013 at 1:05 am
Sean Lange (6/6/2013)
... In lieu of using a permanent table I use a view that creates the tally table on the fly. 0 reads to access it and I don't have to remember the syntax over and over....
It works well as an iTVF too!
ALTER FUNCTION [dbo].[InlineTally]
(@RowCount INT)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
(
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
E2(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c), -- 10 x 10 x 10 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b) --1000 x 1000 rows max
SELECT TOP (@RowCount) n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
GO
SELECT * FROM dbo.InlineTally (100000)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 7, 2013 at 7:40 am
ChrisM@Work (6/7/2013)
Sean Lange (6/6/2013)
... In lieu of using a permanent table I use a view that creates the tally table on the fly. 0 reads to access it and I don't have to remember the syntax over and over....
It works well as an iTVF too!
ALTER FUNCTION [dbo].[InlineTally]
(@RowCount INT)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
(
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
E2(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c), -- 10 x 10 x 10 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b) --1000 x 1000 rows max
SELECT TOP (@RowCount) n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
GO
SELECT * FROM dbo.InlineTally (100000)
That is pretty cool too. Nice to have either option.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy