Dynamic WHERE Clause within a SQL Stored Procedure
-
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
BEGIN
SET @FromDate = NULL;
SET @ThruDate = NULL;
SET @SQLWhereString1 = 'WHERE GRPSPAN.GROUP_NBR = ' + '''' + @GroupNbr + '''';
END
And then I have in my WHERE clause...
WHERE @SQLWhereString1
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.
PSULionRP
All Replies
-
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 KorotkevitchOption recompile
Option recompile discussion threadFor every expert, there is an equal and opposite expert. - Becker's Law
My blog
.png)
