It will be helpful if you attach also sample data, expected result and the version of SQL Server that you are using.

Try pushing the CASE expression into the CONTAINS function.

... IN (

select docno from ... Where contains(case when @type ... End, @var)

);

Remember that the type of those columns should be compatible...

Thanks, Erland!

I decided to give it a try after seeing your post in the private group about your move.

--

AMB

It is difficult to guess just by reading the query. It will be helpful if you describe in words what is it that you are trying to accomplish.

Something that call...

Try adding OPTION (RECOMPILE) to the second query or use sp_executesql with parameters to do the comparison.

SQL Server uses different approach to estimate cardinality when you use parameters or constants...

Keep in mind that when you use EXCEPT you get distinct rows, so if you want to mimic it using NOT EXISTS (counting you have duplicate rows) then you will...

quote:

---

Read this outstanding article.

Dynamic Search Conditions in T-SQL

An SQL text by Erland Sommarskog, SQL Server MVP.

http://www.algonet.se/~sommar/dyn-search.html

AMB

---

September 26, 2003 at 1:41 pm