## Inline Table Valued Function

 Author Message soulchyld21 SSC Rookie Group: General Forum Members Points: 34 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 appreciatedSoulchyld logitestus Mr or Mrs. 500 Group: General Forum Members Points: 517 Visits: 522 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. ChrisM@Work SSCoach Group: General Forum Members Points: 16061 Visits: 19529 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.MyStringFROM (SELECT MyInt = @StartNum + 1 UNION ALL SELECT MyInt = @StartNum + 2 UNION ALL SELECT MyInt = @StartNum + 3 UNION ALL SELECT MyInt = @StartNum + 4) dCROSS APPLY (SELECT MyString = CASE WHEN d.MyInt%2 = 1 THEN 'Odd' ELSE 'Even' END) xGOSELECT * 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 ShawFor 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 ModenExploring Recursive CTEs by Example Dwain Camps soulchyld21 SSC Rookie Group: General Forum Members Points: 34 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? Luis Cazares SSCoach Group: General Forum Members Points: 16284 Visits: 19059 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.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 soulchyld21 SSC Rookie Group: General Forum Members Points: 34 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? Ed Wagner SSCoach Group: General Forum Members Points: 16463 Visits: 10051 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 PersonifiedString Splitting with True PerformanceBest practices on how to ask questions Sean Lange One Orange Chip Group: General Forum Members Points: 25869 Visits: 17509 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 asWITH 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.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) Ed Wagner SSCoach Group: General Forum Members Points: 16463 Visits: 10051 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 PersonifiedString Splitting with True PerformanceBest practices on how to ask questions Sean Lange One Orange Chip Group: General Forum Members Points: 25869 Visits: 17509 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.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)