This is my bread and butter method now.  2000 = OPENXML.

2005 has some different xml functions.  Those should be explored.

..

One of the benefits is that indexes are updated after the...

BigSam,

Samples are provided (at the bottom) of the article.

Here is your specific one.

print 'Filter on specific Countries and OrderDate'

EXEC uspOrderDetailsGetByXmlParams '

<ParametersDS>

 <CustomerCountry>

  <CountryName>USA</CountryName>

 </CustomerCountry>

 <SingleValueParam>

  <OrderDateAfter>1/1/2006</OrderDateAfter>

 </SingleValueParam>

</ParametersDS>

'

I think the cast as int might be the way to go... after working with it so long.

Thanks for all input.

Wow!  Thanks Tom for the udf.  Its perfect.

I didn't read this whole post, but when I got to the part "why is this bad design?", I stopped to post a reply.

You are violating a rule called "1st...

Just to follow up, if you do a google search for:

"How to Optimize the Use of the OR Clause When Used with Parameters"

This guy talks about the OR issue....which is...

I read this article.  It discusses...one of the main issues of the db becoming a bottleneck, and a case for alot(if not all) business logic in the business logic layer.

Ok... you have your opinion, I have mine.  The poster can determine their own opinion, I'm just posting information.

My textbook for my Grad Class at NCSU

http://www2.acs.ncsu.edu/reg_records/crs_cat/CSC.html#CSC540

was this book:

http://www.amazon.com/gp/product/0321122267/102-1404998-1004146?v=glance&n=283155

"Data tends to persist".

That's the point, I think.  It needs to persist.

That's what DB are used for.  Persisting data effectively.

..

This image (from one of the links I posted) has a...

Try these articles:

http://www.devarticles.com/c/a/ASP.NET/Boosting-Your-.NET-Application-Performance/1

or

http://www.15seconds.com/issue/011219.htm

The "DataTier" should only do things like

Return DataReaders

Return DataSets

Return Scalars

Execute Stored Procedures, by sending parameters in.  It should not...

I'll post this, and label it, the 0:N optional parameter problem.

There is a small performance hit to use this method.  I usually use this method for reporting, since the User may...

My advice is to start with MSDE (free), and then when you reach that critical point, break down and buy sql server.

MSDE is basically a lite version of Sql Server...

Yeah, the IN clause is what you're missing.

Here is a sample (pretty much like above , but I had it laying around)

You can substitute a "real" table for @holder.

Use pubs

Go

declare...

This is a guess.

But, make sure you don't have any triggers on those tables (are you the only developer?)

Triggers will screw you.

Another area of error, is using cursors inside your...

I keep this site bookmarked:

http://www.connectionstrings.com/

Sql Server.

OLE DB, OleDbConnection (.NET)  (Try these , even if you're in VB6 )

Use the ODBC ones as a last resort, since that introduces an...