Friday, February 01, 2013 3:12 PM
I have to build a dynamic WHERE Clause within a SQL Stored Procedure. All of my SQL is native...meaning at this point it is NOT dynamic. I am wondering if I can build my WHERE clause based on the parameters that are provided. Right now I have this...IF @GroupNbr IS NOT NULL
SET @FromDate = NULL;
SET @ThruDate = NULL;
SET @SQLWhereString1 = 'WHERE GRPSPAN.GROUP_NBR = ' + '''' + @GroupNbr + '''';
And then I have in my WHERE clause...
And that is highlighting with the error...
"An expression of non-boolean type specified in a context where a condition is expected."
Any help is greatly appreciated and Thanks in advance for your review and am hopeful for a reply.
Friday, February 01, 2013 3:20 PM
You can either make the entire statement as dynamic SQL (which has it's own set of things to know before simply doing it), or, you can do something like:
Where (GRPSPAN.GROUP_NBR = @GroupNbr Or @GroupNbr Is Null)
Erland has a very informative blog on the subject: http://www.sommarskog.se/dyn-search-2008.html
- Proposed As Answer by Naomi NMicrosoft Community Contributor, Moderator Sunday, February 03, 2013 4:58 AM
Sunday, February 03, 2013 4:59 AMModerator
Check these links (any of them) - they will help you solve your problem:Do you use ISNULL(...). Don't, it does not perform - short blog by Denis Gobo
Dynamic Search Conditions in T-SQL Version for SQL 2008 (SP1 CU5 and later) - long and comprehensive article by Erland Sommarskog
Catch All Queries - short blog by Gail Shaw
Sunday T-SQL tip: How to select data with unknown parameter set Nice blog by Dmitri Korotkevitch
Option recompileOption recompile discussion thread
For every expert, there is an equal and opposite expert. - Becker's Law