30 seconds for dropping and creating over 200 procs does not sound unreasonable. It starts to get crazy when you have to run this on 1,000 db's. Suddenly you are...

Easiest solution is to stop using the text datatype. It is deprecated and will no longer be supported in the future. Instead you should use (n)varchar(MAX).

jcb (3/20/2012)

---

Gail,

I had found:

WHERE (key1 = @key1 AND @key1 IS NOT NULL)

OR (key2 = @key2 AND @key2 IS NOT NULL)

OR (key3 = @key3 AND @key3 IS NOT NULL)

makes better execution...

codebyo (3/20/2012)

---

SQLKnowItAll (3/20/2012)

---

Why would you use both DISTINCT and GROUP BY? It is redundant for results if you are searching for true duplicates.

EDIT: Sorry, you did not mention DISTINCT in...

OH LORD NO!!! :w00t: I was just showing you...

You can't give your group by derived column an alias. Remove the "as Region" and you should be fine.

I think you maybe misunderstood what I was saying. Using...

joe.cahoon (3/20/2012)

---

Thanks! I tried the Cast you mentioned above and it worked. Now the only thing is that it gives me the date in this format: 2010-12-03 00:00:00.000. All I...

Well it certainly isn't parameter sniffing...it is all the RBAR going on. There is no need to have nested loops for these inserts. If you want some help making this...

drew.allen (3/20/2012)

---

Just to...

Thanks for the reminder Gail. I did not realize the performance was much of an issue when there is only 1 variable with the approach I took. I knew that...

It looks like maybe you are trying to connect to sql express? Did you use a custom instance name for SQL express?

This article may provide some insight, assuming the services...

drew.allen (3/20/2012)

---

With only one parameter, the dynamic SQL option is probably overkill. Here is another option that I have seen, which is fine with only one parameter.

IF @StaffID IS...

If you want an even faster splitter...and I mean orders of magnitudes faster...take a look at the second link in my signature. It explains why looping structures are horribly inefficient...

To make this SARGable you should change this to not use the coalesce.

Like this.

SET @staffid = 1000

SELECT * FROM TABLE WHERE Staffid = @staffid or @staffid is null

This will let...