CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
PingPing
PingPing

Reputation: 999

T-SQL stored procedure to return all records when input parameter is 'ALL'

I have a stored procedure that has the parameter:

@desk VARCHAR(50)

I want to

SELECT *
FROM dbo.Desk
WHERE DeskName = @desk

and I also want to return all records when the value for @desk = 'All'.

What's the best way to do this?

Upvotes: 1

Views: 3333

Answers (4)

Kamak
Kamak

Reputation: 1

Declare @Desk varchar(50)

if @Desk = 'All' set @Desk = null

select * from dbo.desk where deskname = coalesce(@desk, deskname)

Upvotes: -1

SHAKIR SHABBIR
SHAKIR SHABBIR

Reputation: 1295

The best & easy approach:

SELECT * FROM dbo.Desk WHERE deskName = CASE WHEN @desk='All' THEN deskName ELSE @desk END

Upvotes: 0

MatBailie
MatBailie

Reputation: 86706

As @MartinSmith pointed out, the field = @var or @var = 'ALL' type of answer has a huge down-side...

When the stored procedure is compiled, it generates a single execution plan. And that execution plan needs to be sufficient to handle all scenarios that the parameters can throw at it.

In this particular case you probably (MartinSmith says definitely) scan the whole table every time. This is because such an execution plan is capable of fullfilling both possible requirements.

So effectively, you end up with a single least-worst case execution plan.


To get a different plan for each case (@desk = 'ALL' and @desk != 'All') you need to have two queries. This means that, unfortunately, longer simpler less elegant code is significantly better performing code...

IF (@desk = 'ALL')
  SELECT * FROM dbo.Desk
ELSE
  SELECT * FROM dbo.Desk WHERE deskName = @desk


The link posted by @MartinSmith, in his comment to the Question, goes into this in a whole lot more detail. Depending on your level of SQL experience, it's highly recommended reading; it explains a lot about the behaviour of SQL Server. But it is very in depth.

A more simplistic approach is simply; try it and see.

Create a table with a realistic data-set for your purposes, with appropriate constraints and indexes. Then, create a stored proc for each possible answer, and test them. Ideally using the profiler to see CPU Time, Actual Time, Reads, Writes, etc.

You may see that the difference is so small you can live with the "worst" answer and benefit from it being short (and so making it the "best" for you).

You may see one is shorter in Actual Time, but vastly higher in CPU Time. If you have high concurrency, you may therefore decide to go with the lowest CPU Time. Or, if you have low concurrency, higher CPU Time but lower Actual Time may be preferrable.


Programming is wonderful, so many considersations, trades and balances. Even with a piece of SQL as simpel as this :)

Upvotes: 2

David M
David M

Reputation: 72870

Just include this condition in your WHERE clause like so:

SELECT  *
FROM    dbo.Desk
WHERE   DeskName = @desk
OR      @desk = 'All'

As Martin Smith comments below, the performance of this won't be optimal - if the table is small, then you may prefer this concise approach over a more performant option simply for the sake of clarity/maintainability of your T-SQL code, but do also take a look at the article he links to...

Upvotes: 5

Related Questions