December 27, 2011 at 7:00 am
Hi all,
I have scenario below...
Function Name : OrgUserID(@UserID, OrgId)
This function returns OrganizationUnitId , LevelCode , LevelDescription , Level2Description
I have my Input parameters in Table : T_User_Org
T_User_Org table Structure is UserID, OrgId
Now i need to execute the function OrgUserId for every row in T_User_Org and insert the output values into some table.....
Please throw some light on this...
December 27, 2011 at 7:10 am
Why use a function for this? Cant you do it directly from the table instead of using a function. When you want to use function you probably will have to do a row by row operation but if you are doing it directly from the source tables, you could make it into a set based operation.
Just my 2 cents
-Roy
December 27, 2011 at 7:17 am
No Roy i cant do what you suggested....bcoz there is lot of joins and logic happening inside the function...
I just have to pass the parameters and union all those result and insert into a table...
December 27, 2011 at 7:20 am
Are you familiar with using the Apply operator (Cross Apply and Outer Apply)?
With that, you can "join" a function to a table and pass in the values from the table as input parameters.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 27, 2011 at 7:20 am
Functions = slow. Avoid unless there really is absolutely no other way.
You haven't given us enough info to help you here. Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 27, 2011 at 7:22 am
indraneelandhavarapu-866099 (12/27/2011)
No Roy i cant do what you suggested....bcoz there is lot of joins and logic happening inside the function...I just have to pass the parameters and union all those result and insert into a table...
Why don't you just copy the function code, paste it into a new query window, join with the table you prepared for user filtering and perform your insert from there on ?
RBAR approach will probably need way more time.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 27, 2011 at 7:27 am
no much familiar.....can just give me some pseudo code...so that i can develop further...
December 27, 2011 at 7:30 am
select *
from dbo.MyTable
cross apply dbo.MyUDF(MyTabe.Col1, MyTable.Col2) as MyUDF;
That's Cross Apply pseudo-code. You'll get better examples if you look up "Cross/Outer Apply" in Bing/Google/whatever. MSDN has articles on the uses.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply