Hi Kevin

>However, I took another look at the execution plan tonight, and did some reading - apparently SQL Server, even 2005, doesn't handle COALESCE (or even ISNULL) as efficiently as one using it would hope. (Apparently DB2 handles it better, or at least so I read.)
>
>In SQL Server 2005, the most efficient way to handle parameters (where only some are filled in) in stored procedures is to do the following:
>
>

>WHERE (City LIKE @City  OR @City is null) AND (Address LIKE @Address OR @Address is null)
>

I'd like to know something, and if you're going to test it, please try (pseudo code only)

set @City = null
set @Address = '%Eldorado%'

sqlcmd = 'select name from companies WHERE '

if not is null @City 
  sqlcmd = sqlcmd + 'City like @City'
endif

if not is null @Address
  sqlcmd = sqlcmd + 'Address like @address'
endif

sp_ExecuteSQL(sqlcmd)

There are 4 combinations here. City, Address, City+Address, neither City nor Address. I assume you have to use EXEC to run that because in those companies that demand Stored Procedures only, they disable running dynamic SQL? If so, how does Crystal Reports do anything?

Assuming you can use sp_ExecuteSQL and that the execution plans for all 4 are cached, is there any difference between that and the OR @address is null technique you're using?

>2) There's been this discussion for some time now, about whether stored procs are "flexible". I think I've made a strong case that straight stored procs can be as flexible as dynamic SQL.

Stored Procs can be flexible. No doubt about that. But you're missing one major point. A Stored Proc (except for SP_ExecuteSQL) serves a particular purpose. It therefore cannot be as flexible as hand-written SQL. A dynamically constructed SQL Command serves any purpose desired.

Look at it this way. If I said to you here are my FoxPro UDFs that access data. You are not allowed to use REPLACE, SELECT-SQL, SEEK, but must call my pre-canned UDFs. How would you like that?

>3) With performance being close, stored procedures are more about building a consistent API into your database, one that is client agnostic. Maybe in shops that are small in terms of developer staff, and/or only have one primary development tool, this may not seem like a strong argument. But scaling out dynamic SQL in more complex environments is going to be problematic - which is likely why the use of stored procs generally goes up in larger companies.
>
>So I think the speed of one or the other isn't nearly as big a concern, when compared with the management of database code. Or....sometimes it comes down to choice - whether it's a choice that a developer is allowed to me, or being able to use either approach if the choice has been made for him/her.

Overall I agree, but even this model is lacking. It was once common practice encode printer commands directly into Fox code. That is a two-tier approach. Application and Printer. Printer drivers split the model into 3 tiers.

Where is the third tier in these stored-procs only designs?