"There is no rule that says: SP is always better than a dynamic query."

At the moment you're only looking at the performance angle...throw in the security issues generated by D-SQL...

"(maximum 3 or 4 number of joins are recommended)"....recommended by whom?  Please support by evidence.

SQL is powerful enough not to have a problem with joins involving far more tables than...

Interesting that bankers sometimes get to be fair..."...the point of banker's rounding isn't to be accurate, it's to be fair"...normally we're accused of far worse crimes.  (Though I suspect it...

Agreed.  This has additional benefit if the relationship ends up being a many-2-many...ie if over time an employee can work on more than 1 project for this then you just...

2 issues

1st....can a user belong to more than 1 AD security group...(for different reasons).  Technically I think the answer would be yes....so I think you need to cater for looping through...

posting some DDL, sample input data in the form of insert statements and matching expected results will advance any investigation process must faster. 

I totally agree....get rid of the cursor!...You're...

"NOTE: You can wrap all the above in a transaction and roll it back if if needed.  (don't forget to commit)."

Not quite true....remember the non-logging/minimal logging effect of the TRUNCATE....that does...

Why not change the "loop within a loop" style operation to be a left join...across the 2 tables...and do the action all in one go?

You may wish to bring this to the attention of "PaulRandal"....a member over at SQLTeam.com.  Paul's a Microsoft head who wrote some of the internals of the SQL Engine.  He...

can't see anything "obvious" wrong in the logic...i can see a need for the cursor...as the output/effect of 1 row effects the next row being processed but can't obviously see...

search also for "parameter sniffing" and solutions to same.

Can you post the full SP?...and maybe a brief description of what its trying to do.  You can hide/obfuscate any identifying remarks/code if there are confidentiality issues at play.

Does this affect you?

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=263&messageid=349578

Is this a side-effect in advance?...a bit like the blast-wave that precedes a bomb

...2 daily emails for the same day, with different content

Service...

1. Have a look at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=13574

2. Build you own dictionary of "related" words....including legit variations and misspellings

 a) there's a finite number of words of interest to you

 b) by recursively...