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

asked 22 Jul '16, 07:15

Jongee's gravatar image

accept rate: 0%

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
permanent link

answered 22 Jul '16, 07:50

Volker%20Barth's gravatar image

Volker Barth
accept rate: 33%

edited 22 Jul '16, 07:56

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;
  SELECT forename, surname, jobtitle, divname
  FROM dba.contact
  WHERE createdby = @user;

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

(22 Jul '16, 08:29) Mark Culp
Replies hidden

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?

(22 Jul '16, 08:49) Volker Barth

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.

(22 Jul '16, 09:48) Mark Culp

Thanks for all the additional comments.

(27 Jul '16, 09:16) Jongee
Your answer
toggle preview

Follow this question

By Email:




Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • numbered list: 1. Foo 2. Bar
  • basic HTML tags are also supported

Question tags:


question asked: 22 Jul '16, 07:15

question was seen: 1,187 times

last updated: 27 Jul '16, 09:16