Absolutely! It was cut-&-paste issue. Didn't put the second one after unconditional set noexec off.

if (OBJECT_ID('p_a') is null) SET NOEXEC OFF;

GO

if (OBJECT_ID('p_a') is not null) SET NOEXEC ON;

GO

create proc...

Please try to post your question in accordance with forum etiquette, you can find the link at the bottom of my signature.

You can use following query to get the list...

Ugly is a very subjective view.

In my opinion it's quite extravagant 🙂

It's usefull when you want to stop batch script...

You need to be a bit more creative with your T-SQL batch to achieve what you want :-D:

if (OBJECT_ID('p_a') is null) SET NOEXEC OFF;

GO

if (OBJECT_ID('p_a') is not null) SET...

Ok, your "results start coming instantly" means nothing, as most likely they will take even longer to come out in full than SELECT INTO. 1.8 millions should not take 2.30...

I would try to SELECT INTO #table the result of the query including the calculated columns used for grouping (without grouping itself), then the final query would select from #table...

It was just a guess which worked, however it could be down to the code inside of proc. It is always good to post code when asking performance question!

Also, playing...

Post your proc code!

Try:

SET ANSI_NULLS ON

GO

Before creating your proc.

So what? You can use temp # tables in dynamic sql.

What is you point?

You can optimise the solution even further. Few tips:

1. Create Error table on fly using SELECT INTO and UNION ALL

2. You may find that before loading data to the final...

J Livingston SQL (2/9/2012)

absolutely agree...but should it be...

Could you please provide the full query at least, please. Grouping is relatively expensive operation as well as string concatination. It's impossible to advise on optimisation without having underlying object...

If you want to do it in the hand crafted T-SQL, you can do the following:

Import all data into the staging table with reasonbaly maximum unrestricted varchar datatype for ...

Jeff, that why I said "one of". I was afraid to recommend using "HAVING COUNT..." as it could be even harder to explain to his "instructor".

However, if...

Ask your "instructor" what will happen if two different states have the city with the same name?

The solution given to you is not only wrong, but also would perform badly...