Walter, I ran some more tests, using VFP 9 to SQL 2005 with dynamic SQL, and then straight SQL 2005 SP.

First, using VFP....the code that winds up being sent to SQL looks like this (if you wanted to grab everyone in NY whose lastname begins with 'Williams':

exec sp_executesql N'SELECT * FROM Customers WHERE sTATE = @P1  and LASTNAME like @P2 ',N'@P1 varchar(2),@P2 varchar(50)','NY','%WILLIAMS%'

And then the guts of a stored proc:

declare @lastname varchar(50), @firstname varchar(50),  @address varchar(50), @city varchar(50), @state varchar(2), @zip varchar(13)

SET @LastName =  'WILLIAMS%'   
SET @FirstName = isnull(@Firstname,'%')
SET @Address = ISNULL(@Address,'%') 
SET @City =  isnull(@City, '%')
SET @State = 'NY'
SET @Zip = isnull(@Zip,'%')

SELECT * FROM Customers  WHERE         (LastName LIKE @LastName  ) AND
                                       (FirstName like @FirstName  ) AND
                                       (Address like @Address  ) AND
                                       (City like @City ) AND 
                                       (State like  @State  ) AND
                                       (Zip like @Zip  ) 

Obviously, I was mistaken about COALESCE, it didn't work as well as I thought. It never performed a table scan but did perform an index scan. My test table is 1 million records, and while the difference is less than a second, the above is definitely better.

I've done a number of different combinations, and the execution plans are the same - both the dynamic SQL and SP use index seeks and RID Lookups. The only mathematical difference is the % of cost...with the stored proc, it's 50%/50%, whereas with dynamic SQL, the index seek is 40% and the RID Lookup is 60%.

There are other tiny things, such as any overhead using SQLEXEC vs any overhead to call a stored proc using .NET....so from my perspective, both approaches yield the same results.

If someone wants to use dynamic SQL to construct a query where there are optional parameters....or if they prefer stored procedures (or must used stored procedures because table access is not provided)....as far as I'm concerned, both can yield the same results.