Table Function question

Question

Table Function question

adam-639168

Default port

Points: 1444
More actions
April 24, 2015 at 12:58 pm

#398373

There is a table function that returns results based on the account parameter that is referenced.
e.g. select * from dbo.mytablefunction(ACCOUNT)
I have a list of accounts in a different table that I'd like to run this table function on and return results for all of the accounts at once, rather than me having to do this one at a time for each account.
Is there a way to do this? Changing the table function isn't an option.
Thanks in advance.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

adam-639168 (4/24/2015)
There is a table function that returns results based on the account parameter that is referenced.
e.g. select * from dbo.mytablefunction(ACCOUNT)
I have a list of accounts in a different table that I'd like to run this table function on and return results for all of the accounts at once, rather than me having to do this one at a time for each account.
Is there a way to do this? Changing the table function isn't an option.
Thanks in advance.

nothing built in, you'll want to go to the definition of the function, and copy it to create an adhoc query or a new function.

it sounds like internally it is doing nothing more than

SELECT SomeColumns

FROM SomeTable

WHERE SomeColumn = @account

so you'd just modify it something like this, for example

SELECT SomeColumns

FROM SomeTable

WHERE SomeColumn IN(SELECt Account From ListOfAccounts)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

adam-639168 Default port Points: 1444 More actions · Answer 2

Thanks, I was hoping there would be some kind of loop (beyond my level) or something that could iterate through each account.

Ideally, I'd like to avoid creating a duplicate query (more maintenance), but your solution would certainly work if nothing else will. Thanks for the idea.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

adam-639168 (4/24/2015)
Thanks, I was hoping there would be some kind of loop (beyond my level) or something that could iterate through each account.
Ideally, I'd like to avoid creating a duplicate query (more maintenance), but your solution would certainly work if nothing else will. Thanks for the idea.

You need a loop but not the kind you're thinking of. Something like the following will do the trick using the natural loop built into a SELECT along with the capabilities of CROSS APPLY calling a TableValuedFunction. Hope it's an iTFV (inline Table Valued Function) and not an mTVF (multi-statement Table Valued Function) or it's going to be slow no matter what.

SELECT ca.*

FROM dbo.DifferentTable dt

CROSS APPLY dbo.mytablefunction(dt.Account) ca

;

The cursor/natural loop behind each select runs at near machine language speeds and is called a "Pseudo-Cursor" for short. CROSS APPLY is nothing more than a correlated sub-query capable of return multiple rows for each row in the SELECT.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

adam-639168 Default port Points: 1444 More actions · Answer 4

Thanks Jeff. I will look into the cross-apply solution. I always learn something new when I'm here 🙂

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

That's the whole idea here! 🙂

Thank you for the feedback and please post back if you have more questions on this problem. And, if the function has the word "BEGIN" anywhere in it, you might want to post it so we can try to convert it to something faster for you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)