dwain.c (9/26/2013)

---

The short answer is yes, however I have several issues with what you're trying to do.

1. [Issue #1] In the statement below, you're setting the right part of the quality to a characters string but unless @SexID already contains embedded quotes the dynamic SQL you're constructing is going to fail.

SET @SqlQuerySex = ' WHERE sexID = ' + convert(varchar(20), @sexID)

2. [Issue #2] What happens if more than one of the query parameters has been sent through? It seems that you'll have a WHERE keyword in multiple places in your dynamic SQL.

3. You can get around issue #2 quite easily (and answer you're original question) by simply starting with the basic SELECT/FROM in a string and then using CASE to attach to it the additional query parameters. Include the WHERE only if any of the filter parameters are specified.

4. [Issue #3 and solution to issue #1] Instead of assigning the value to the SQL string for filter parameters, consider checking for field = @Filter, and then passing @Filter to sp_executesql. Check BOL for a description of how to do that (I'm guessing that you were planning on using EXEC (@SQL) instead).