CODEWITHSUNDEEP
sqlsql-servert-sql
user15016847
user15016847

Reputation: 1

Stored procedure input parameters = "All"

What does it mean when you set an input parameter of a stored procedure to 'ALL'?

For example:

WHERE desk = @desk OR @desk = "ALL"

Is All a built-in SQL keyword? Like can you simply specify All in any stored procedure without having to adapt your table data, and it will simply return all records?

Upvotes: 0

Views: 577

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Yes, ALL is a SQL keyword. It can be used in contexts such as:

where x >= all (select y from t)

However, "ALL" is not a SQL keyword. The only way it would be accepted by SQL Server in this context is if it were a column in a table.

I doubt that is the case. I would guess that what you really intend is a string:

WHERE desk = @desk OR @desk = 'ALL'

Note that single quotes are quite different from double quotes.

In this context, it is just a string and a convention for whoever wrote this code to get everything. It is standard in no way. In fact, although there is not a standard, NULL is typically preferred:

WHERE desk = @desk OR @desk IS NULL

NULL has two advantages over 'ALL':

  • It works for all data types.
  • It cannot be confused with an actual value, because = doesn't work with NULL anyway.

Things get a bit trickier if you want to also test for NULL values, but that is not the question you asked here.

Upvotes: 1

Frendy Lio Can
Frendy Lio Can

Reputation: 21

Like Aaron said, "ALL" is not a keyword.

If you want to return ALL records if the keyword "ALL" is passed, you can do the following:

WHERE 
   1 = CASE 
          WHEN @desk="ALL" THEN 1
          WHEN desk=@desk THEN 1
          ELSE 0
       END

Upvotes: 0

anon
anon

Reputation:

No, it is not a keyword. That is saying that if the user provided the word 'ALL' as the parameter for @desk, then the column desk shouldn't be checked. In English, that query reads (reading backward):

If the user provided the string "ALL", then they want to see all desks.
Otherwise, check for the specific desk they asked for.

Performance is not great for one optional parameter, just wait until you get to 10 optional parameters. See some background:

(In those cases you can consider NULL to be the equivalent of the word 'ALL' - they both represent that the user doesn't want to filter on that column.)

Upvotes: 1

Related Questions