I've changed the ORDER BY on one of the statements and it seems to have fixed it:

IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.SCHEMATA

WHERE SCHEMA_NAME = 'utils'

AND SCHEMA_OWNER = 'dbo')

BEGIN

...

Shamshad Ali (8/23/2010)

---

Hello,

The script you make with efforts are highly appreciated, but the latest script I checked for my case is not perfect and have bugs. All, always verify...

Nice article with gentle introduction. thanks.

janyalee (6/21/2010)

---

Using a Certificate Versus Stored Procedure WITH EXECUTE AS

Hello Jonathan, I appreciated this article. It got me wondering, what signing with a certificate gets us that SP WITH...

UMG Developer (6/21/2010)

---

But I don't see how either using the certificates or the proxy login protects you from SQL injection attacks when using dynamic SQL, as the SP can still...

ChiragNS (6/21/2010)

---

We granted select permissions to mycertificateuser. Do we need to give permissions to all the other users who are going to execute the procedure (i think not.. thats the...

Adam Gojdas (5/25/2010)

---

Something that often goes overlooked when using sp_executesql is explicitly using unicode strings. It is good to keep in mind the SQL and ParmDefinition parameters are of...

James Dingle-651585 (5/24/2010)

---

It is difficult to demonstrate for me now because I am not at the office, the computer I use now does not have SQL... sorry if I take...

James Dingle-651585 (5/24/2010)

---

Hi Jon,

Quick remarks:

- There is a clustered index scan on the ID. Obviously, this way it won't work. The snippet of code I've wrote was assuming...

And just one more before anyone else suggests it:

-- Query 10

SELECT *

FROM [Product]

WHERE NOT([Name] <> @Name AND @Name IS NOT NULL)

the where clause above is actually...

James Dingle,

In fact your query preforms nearly twice as badly as the original non-dynamic SQL that would have been in the initial stored procedure.

See SQL and execution plan below.

DECLARE @Name...

James Dingle,

I've tried your style of query to obtain the execution plan and it is the worst performing out of all the attempts so far.

Please find details below. I created...

I thought I'd better put some tests in after all the discussion.

Create some test data:

IF OBJECT_ID('Product','U') IS NOT NULL

DROP TABLE [Product]

IF OBJECT_ID('Colors','U') IS NOT NULL

...

chris.shaw (5/21/2010)

---

Jonathan,

I must say that I found your article to have some really good information in it. I guess there was only a couple comments to it...

Gursoy Yerli (5/11/2010)

---

Fixed a bug;

Your generation sproc was ignoring the schema when using "EXEC sp_helptext @ProcedureName". Added below to fix the issue.

...