0

I have a one-to-many relationship between two tables(say something like User(1) and Orders(N) for the sake of simplicity, in reality a little different).

Now, I'm passing every record's ID in a user defined function, which with a subquery to Orders table loads all child entities for the current user and based on some condition returns 0 or 1, to filter the user out or in the result set. Now, while it is working, this subquerying is highly inefficient and takes forever. Is it possible to somehow join the two tables, and pass in the child data in the function (without it having to load it all again).

Something like

SELECT * from Users AS u1
WHERE IsMatch(u1.Orders) = 1 // this should pass in say the 5 orders the user has

Yes, invalid and it looks like something I would be able to write in C#, but I'm looking for something like this in SQL. Currently this is where I pass in the ID of the user and the IsMatch function makes a subquery to the Orders table to load the data for the current user to check some condition on them.

So to sum up: how I can pass an entity's child data in a user defined function, doing so from another select query?

user2574170
  • 13
  • 4
  • We need some more details here. What is the code for IsMatch? Also, scalar functions are notoriously bad for performance. An inline table valued function would be much better. Then to get the results for each row you need to use APPLY. – Sean Lange Aug 26 '15 at 16:02
  • so you're trying to pass a table or set of rows to a function?.. not sure that's an option.. you can however pass xml to a function.. performance wise, you may just want to skip the function and put your logic in your proc – JamieD77 Aug 26 '15 at 17:47
  • Passing orders to the function would end up doing a sub-query anyway. So this mean it won't make the performance better. Do you still want this question to be answered? – AXMIM Aug 26 '15 at 19:12
  • If so, have a look over here : http://stackoverflow.com/questions/1609115/pass-table-as-parameter-into-sql-server-udf – AXMIM Aug 26 '15 at 19:13

0 Answers0