The forum will be down for maintenance over the weekend of August 18-20, 2017. The forum will be shut down on the evening (EDT) of Friday, August 18. Downtime is unknown but may be up to two days. The forum will be restarted as soon as maintenance is complete.


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
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
  SELECT forename, surname, jobtitle, divname
  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:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 22 Jul '16, 07:15

question was seen: 329 times

last updated: 27 Jul '16, 09:16