If parameter is null then show all records

[former_SQLA_member1694895]

Hi

I am wanting to write a query that shows accounts related to a selected user createdby but only if a user has been entered, if not I want to show all users.

An example of my code so far can be seen below. I just need to know what goes where the '%' is.

SELECT forename, surname, jobtitle, divname
FROM dba.contact
WHERE createdby = case when @user is null then '%' else @user

[VolkerBarth]

In order to use wildcards like '%' in a comparison, you have to use the LIKE operator (or SIMILAR/REGEXP for v11 and above), so the following should work:

WHERE createdby like case when @user is null then '%' else @user

In contrast, in your sample, "createdby = '%'" would only evaluate to true for the value '%' itself.

I'm assuming that createdby is declared as NOT NULL, otherwise you would need to handle NULL values, as well.

---

Note, a more efficient (and comprehensible IMHO) condition, also treating possible NULLs for createdby correctly, would be to use

WHERE createdby = @user or @user is null

[MarkCulp]

Note that using either of Volker's suggestions will require a table scan since the predicates are not sargable and therefore a table scan will be required.

An even more efficient way of handling this case would be to test if @user is null outside of the query and then query all users or just the one appropriately. I.e.

IF @user is NULL THEN
  SELECT forename, surname, jobtitle, divname
  FROM dba.contact;
ELSE
  SELECT forename, surname, jobtitle, divname
  FROM dba.contact
  WHERE createdby = @user;
END IF;

Provided there is an index on createdby then the ELSE clause query can do an easy index lookup.

[VolkerBarth]

Mark, could you please tell why "WHERE createdby = @user or @user is null" is not sargable? - I would think the optimizer will decide whether @user (treated as a constant for that query) is null or not and would then use the sargable predicate "x = someValue" (in case x is indexed, of course) or would return all rows (thereby necessarily doing a table scan) otherwise.

Or do you relate to ASA9 which might work different than current versions here?

[MarkCulp]

Volker: You are correct. I tested v17 and it showed that the latest 17.0.4 optimizer is smart enough to do some work with the constants to eliminate predicates and therefore your suggested solution is good. I did the same test with v9 and the query plan each time (1: @user = NULL, 2: @user not NULL) involves a sequential scan. Without more testing (or looking the code or docs) I do not know at what point the smarts were added to the optimizer.

[former_SQLA_member1694895]

Thanks for all the additional comments.