>My second question, a more general one, is this - if I programatically build a SQL SELECT string statement in a stored proc and then call it with the EXECUTE function, am I truly losing performance (as opposed to a more 'constant' SQL Select statement in a stored proc)? Basically, I'm trying to get a handle on which practices and techniques will sacrifice

There are a couple of issues to be aware of when using Dyanmic SQL.

1) The query will be treated as if it was submitted from the client. That is, it will go through the complete cycle: syntax checking, parsing, name resolution, optimization, and finally, execution.

You could help reduce some of this work by using the sp_executeSQL system stored procedure. This system stored procedure will try to cache the query plan created by the optimizer.

2) The Dynamic SQL is treated as a seperate batch. It will have no visibility to variables created within the stored procedure. Something like the following will NOT work:

DECLARE @id int
DECLARE @query nvarchar(4000)
SET @id = 1
SET @query = 'SELECT @id = idcolumn FROM mytable WHERE ...'
EXCUTE(@query)

Only temporary tables will still be in scope.

3) Since Dynamic SQL is treated as a seperate batch, it runs under the credentials of the connection. This means that you'll have to give the users direct access to tables. The perferred practice is to deny direct access to tables and force all manipulations through stored procedures.

-Mike