Aye, dynamic SQL it is...

Thanks very much to all who took time to look at this problem, it's much appreciated!

SQL Kiwi (5/2/2012)

---

The simplification you are expecting with OPTION (RECOMPILE) only works with SQL Server 2008 and later. This is known as the parameter embedding optimization - see http://blogs.msdn.com/b/grahamk/archive/2009/11/18/changed-behaviour-of-option-recompile-syntax-in-sql-server-2008-sp1-cumulative-update-5.aspx

Without...

Grant Fritchey (5/2/2012)

---

Wow that's messed up. The estimated rows are different even though the values are the same. It looks like the variables, despite the recompile and the sniffed values,...

Certainly can.

It would appear the option (recompile) should work, admittedly I'm trying outside of a stored procedure, but that shouldn't make a difference, should it?

Grant Fritchey (5/2/2012)

---

One point to keep in mind, in most circumstances partitioning is not a performance enhancer, but rather a data management enhancer. Performance is frequently degraded somewhat by partitioning....

Hmm, sorry, but I disagree, I think this is pretty damn nasty.

A table for each type of object that is changed each having the same schema?

Columns defined for the...

RyanRandall (9/28/2007)

---

However, for the example presented by the article, can someone tell me what's wrong with simply this?...

select

customerid, companyname, country

from

...

How about:...?

set @companyname = 'A%'

set @companynameOp = 'ne'

set @country = 'Germany'

set @countryOp = 'eq'

set@companyName =

case @companyNameOp

...

One reason never to use this product, just how is

SELECT

@error = @@ERROR

IF

0 = @error

AND

0 < @@trancount

COMMIT

ELSE

ROLLBACK

END

easier to read than

if 0 =...