Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Inline Table Valued Function Expand / Collapse
Author
Message
Posted Thursday, June 6, 2013 5:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 6, 2013 2:25 PM
Points: 24, Visits: 50
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
Post #1460629
Posted Thursday, June 6, 2013 6:02 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 11:25 PM
Points: 240, Visits: 304
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.

Post #1460638
Posted Thursday, June 6, 2013 6:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:56 AM
Points: 7,137, Visits: 13,527
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)



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1460642
Posted Thursday, June 6, 2013 9:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 6, 2013 2:25 PM
Points: 24, Visits: 50
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?
Post #1460748
Posted Thursday, June 6, 2013 9:32 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:19 PM
Points: 3,374, Visits: 7,303
No need for a loop, what you need is a Tally (numbers) table.
The "Numbers" or "Tally" Table: What it is and how it replaces a loop.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1460761
Posted Thursday, June 6, 2013 9:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 6, 2013 2:25 PM
Points: 24, Visits: 50
Thanks, so the idea is to build a tally table on the fly and select from that for my query is that correct?
Post #1460764
Posted Thursday, June 6, 2013 11:16 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 11:29 AM
Points: 3,999, Visits: 3,022
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.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1460824
Posted Thursday, June 6, 2013 12:23 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 13,139, Visits: 11,980
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1460836
Posted Thursday, June 6, 2013 12:27 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 11:29 AM
Points: 3,999, Visits: 3,022
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.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1460840
Posted Thursday, June 6, 2013 12:43 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 13,139, Visits: 11,980
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1460845
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse