Took your initial example and modified it so I could use a pivot with a dynamic set of cols, not sure how much of a hit I'm taking by using...

That's f***ing impressive, I have been working with various langs for more than 15 years and usually you can gauge how complex something is and how much code it might...

Keep in mind that the slow query may extend passed your t-sql here, it could be a result of poorly placed indexes, not enough indexes, fragmentation, the list goes on...

Yes, this is what I was referring to when I mentioned joins

believe that I'm attempting to do the same thing. Are you attempting to something along these lines?

DECLARE @execquery AS NVARCHAR(MAX)

SELECT column1, column2 INTO #TempTable1 FROM table WHERE item1 =...

In clauses are generally slower because people write them instead of joins, so they don't scale well, but in your example they are prob faster than separate or statements.

Take one of the slowest queries and add top 1000 to it and run it without the unions and start picking away the pieces to which is the bottleneck. Change...

I noticed strange behavior using out args with defaults and out arg does not get set.

create proc dbo.BLAH(@ClassId int = -1 out)

if(isnull(@NavClassId, 0) <= 0) set @NavClassId = -1...

I don't think you understand my question as it is not a simple select insert, and of all the examples I've seen I can't find one that does an insert...

A better question might be is there a difference between count(*) and count(col).

Many people don't know the subtleties between them.

The purpose of this was to have flow control during a db upgrade.

I wanted to log that an upgrade file had been run, if unsuccessful the upgrade process would rollback...