>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.

There is one pitfall here. Since passing different parameters from one call to another, it is not wise to use the same executionplan again and again. If the query is optimized for searching on firstname and the next time you search on city, it uses the execution plan for optimizing the firstname and could cause the query to become extremely slow. Therefore it might be wise to use 'WITH RECOMPILE' in the stored procedure.

The dynamic SQL does not have the problem since in the case above it would be two different queries with two different executionplans.

Walter,