inline table function

Question

Post reply

inline table function

sqlfriend

SSC Guru

Points: 52769
More actions
June 11, 2013 at 12:09 pm

#276202

I need to create function that returns a concatenated street.
I first used a scalor funciton, but I read from internet inline table funciton is better.
So how can I convert my scalor function to table function, how can I cal lthe functin by join other tables, also is it possible I can not only get one student street, also get a group of student's street by calling the function?
I attached the script

Viewing 8 posts - 1 through 7 (of 7 total)

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

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

Something like this should work. This is untested because there is no sample data.

CREATE FUNCTION [dbo].[GetStreetApt] ( @StudentID INT )

RETURNS TABLE

RETURN SELECT CAST(ama.houseNumber AS VARCHAR(20))

+ CASE WHEN ama.HouseNumberModifier IS NULL THEN ''

ELSE ' ' + HouseNumberModifier

END + ' ' + ama.FullStreetName

+ CASE WHEN spa.ApartmentNum IS NULL THEN ''

ELSE ' APT# ' + ApartmentNum

END as StreetApt

FROM dbo.stuPropertyAddress AS spa

INNER JOIN dbo.addMasterAddress AS ama ON spa.AddressID = ama.AddressID

WHERE spa.StudentID = @StudentID

Then to use it you can join to it like any other table or you can use cross apply, or...

select pa.*, gsa.StreetApt

from stuPropertyAddress pa

cross apply dbo.GetStreetApt(pa.StudentID) gsa

_______________________________________________________________

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/

sqlfriend SSC Guru Points: 52769 More actions · Answer 2

Thanks, this is what I want for to get a group of student's street.

Then how can I get for the single student street by join other table, I tried this:

DECLARE @studentID INT =4408696

SELECT spa.StudentID, spa.AddressID, itv.streetApt, ama.city, ama.state FROM stuPropertyAddress spa

INNER JOIN dbo.addMasterAddress ama ON spa.addressID=ama.addressID

CROSS apply dbo.FNC_GetStreetApt2(@studentID) itv

it returns multiple records, so what shoudl be the correct syntax?

Thanks

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

You should use something like this

SELECT spa.StudentID, spa.AddressID, itv.streetApt, ama.city, ama.state FROM stuPropertyAddress spa

INNER JOIN dbo.addMasterAddress ama ON spa.addressID=ama.addressID

CROSS apply dbo.FNC_GetStreetApt2(spa.StudentID) itv

WHERE spa.StudentID = @studentID

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

sqlfriend SSC Guru Points: 52769 More actions · Answer 4

Thanks, the two answers is just what I want.

One more question, so for join inline table function we should always use cross apply, is that correct?

we can also use table inline funciton for update statement, what is the syntax of that?

Thanks

Sean Lange SSC Guru Points: 286573 More actions · Answer 5

sqlfriends (6/11/2013)
Thanks, the two answers is just what I want.
One more question, so for join inline table function we should always use cross apply, is that correct?

You should read this article by Paul White. http://www.sqlservercentral.com/articles/APPLY/69953/[/url]. Make sure you read the second part, there is a link to this at the end of part 1.

we can also use table inline funciton for update statement, what is the syntax of that?

No, you cannot update a function. A table valued function or a scalar function only returns data.

_______________________________________________________________

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/

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

Sean Lange (6/11/2013)
sqlfriends (6/11/2013)
we can also use table inline funciton for update statement, what is the syntax of that?
No, you cannot update a function. A table valued function or a scalar function only returns data.

I'm sure he meant something like

UPDATE spa --Table to update

SET SomeAddressColumn = itv.streetApt --Columns to update

FROM stuPropertyAddress spa

INNER JOIN dbo.addMasterAddress ama ON spa.addressID=ama.addressID

CROSS apply dbo.FNC_GetStreetApt2(spa.StudentID) itv

WHERE spa.StudentID = @studentID

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

sqlfriend SSC Guru Points: 52769 More actions · Answer 7

Thanks, that is what I meant to udpate a column and in joins and cross apply use the inline table function.