Optional parameter values, good or not ?

Question

0.00/5 (No votes)

See more:

SQL

declare @Param1 int
-- set @Param1 = -1
-- set @Param1 = 123

-- Field1 is never equal to -1

select field1, * from Table1
  where (@Param1 = -1) or
        (field1 = @Param1)

If you set the @Param1 to -1, then you get ALL data from the table, but if you set the parameter to a specific value then you only get the rows that are equal to that value.

This kind of query is really a time saver for coding. For small tables ( and small quantity of requests) this is really a great way to do it, but I never had the time to look if it is the right way? Because the right way is very important when you have a millions of rows of data and a very large number of requests.

Anyone ???

Posted 29-Dec-15 14:13pm

Riste Nikolov

Updated 30-Dec-15 9:17am

Maciej Los

v2

Add a Solution

Comments

Sergey Alexandrovich Kryukov 29-Dec-15 20:34pm

Not clear what would investing more time in the issue would give you. Do you need more time for thinking, or you think that you have to read some articles on the topic? I suggest to focus on your own thinking, even if you read those articles, which you should approach with critical thinking. My hint for you is: think, if you use optional parameters, what will you lose? :-)
—SA

PIEBALDconsult 29-Dec-15 20:37pm

Looks like false-performance to me. It might be OK for a query you rarely use (and only interactively), but if it gets used a lot by an application, you should do it the right way.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

W∴ Balboos, *GHB* · Answer 1 · 2015-12-30T08:09:00

There's a generic reporting application I wrote. Users can use specific values to fine tune any of an arbitrary number of fields. Fields left empty typically imply that all data in that field is to be accepted.

So, whilst I wrote code to allow these reports the DBA that uses the values in his stored procedures handles these wild-card statement in a case statement (or rather, a series of them), usually in the WHERE clause.

Something like (pretend it's an int field)

SQL

WHERE intCol = CASE @ival
  WHEN -1 THEN intCol
  else @ival
END

The point being that the match everything is accomplished by comparing a column's value to itself. He knows all sorts of performance stuff, so I'd imagine he'd not use this if it were bad practice.

These can also turn up in the SELECT list

See: CASE (Transact-SQL)[^]