Correct usage of WHERE and AND in procedure with optional parameters

Question

Pre-question info:

I'm writing a stored-procedure that would take some parameters and depending on those parameters(if they are filled - because they don't have to be) I'm adding few where clauses. The thing is I don't know if I'm gonna even use the where clause from start because I don't know if any of my params is going to be non-empty/not-null.

The inside of procedure looks cca like:

BEGIN    
DECLARE @strMySelect varchar(max)
SET @strMySelect ='SELECT myparams FROM mytable'        

// add some WHERE statement(*)

  IF(ISNULL(@myParamDate1,'')<>'')BEGIN
  SET @strMySelect =@strMySelect +'
    AND param1 >='''+CAST(@myParamDate1 as varchar(30))+''''
  END

  IF(ISNULL(@myParamDate2,'')<>'')BEGIN
  SET @strMySelect =@strMySelect +'
    AND param1 <='''+CAST(@myParamDate2 as varchar(30))+''''
  END

//... bit more of these "AND"s

EXECUTE(@strExec)

QUESTION: Is it ok(correct way of doing this) to put in my query some WHERE statement that I know that will be always true so I can use in my parameter cases AND always? OR do I have to check for each param if it's first one that is filled or is there an easy way of checking in SQL that at least one of my parameters isn't NULL/empty?

It's fine, it is fairly common to just append `WHERE 1 = 1 `, then you know to use `AND` with all your optional parameters. — GarethD, Jan 26 '16 at 11:57
Everything you need to know and more about this subject can be found in Erland Sommarskog's article [Dynamic Search Conditions in T‑SQL](http://www.sommarskog.se/dyn-search.html) — GarethD, Jan 26 '16 at 13:11

Dan Bracuk · Answer 1 · 2016-01-26T12:52:32.173

2

I handle optional parameters like this:

where (
(@optionalParameter is not null and someField = @optionalParameter )
or
@optionalParameter is null
)
etc

I find it simpler.

edited Jan 26 '16 at 12:52

answered Jan 26 '16 at 11:53

Dan Bracuk

20,699
4
26
43

The thing is this will select also rows that have "somefield" null which I don't want.. if I'm thinking correctly. – Ms. Nobody Jan 26 '16 at 12:47
Actually, it will throw an error on someField = null. I'll edit the answer to fix that. – Dan Bracuk Jan 26 '16 at 12:51
Would it be the same to write: `WHERE(@optionalParameter IS NULL OR someField = @optionalParameter )` ? – Ms. Nobody Jan 26 '16 at 12:56
The problem with this approach is that unless you recompile the query with each execution then the optimiser is not able to use appropriate indexes because of the `OR` conditions. In most cases it is advisable to use `OPTION (RECOMILE)` (I have assumed sql server despite the MySQL tag based on the syntax in the question). – GarethD Jan 26 '16 at 13:10

score 1 · Answer 2 · answered Jan 26 '16 at 12:11

Your extra where clause is not a problem from a performance point-of-view, since the query optimizer will (likely) remove the 1 = 1 condition anyway.

However, I would recommend a solution along the lines of what Dan Bracuk suggested for two reasons:

It is easier to read, write and debug.
You avoid the possibility of SQL injection attacks.

There are cases where you have to custom-build your query-string (e.g. when given a table name as parameter), but I would avoid it whenever possible.

score 0 · Answer 3 · answered Jan 26 '16 at 12:42

You don't need to use EXEC function to check for parameters. A good practice is using case to check for parameter value for example

CREATE PROCEDURE MyProc 
     @Param1 int = 0
AS
BEGIN
     SELECT * FROM MyTable WHERE CASE @param1 WHEN 0 THEN @param1 ELSE MyField END = @Param1
END
GO

In case that @param1 has no value (default 0) then you have @param1=@param1 which gives always true, in case you have @param with value then condition is MyField=@param1.

Correct usage of WHERE and AND in procedure with optional parameters

3 Answers3