• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 363
  • Last Modified:

If statement for SELECT statement

What I would like to accomplish is to have a query pull data with 2 parameters if both parameters have a value.  If only 1 parameter has a value, then only pull based off that parameter.  Here is the original query:

SELECT col1, col2, col3, col4, col5 FROM table1 WHERE col3 LIKE '%' + @ID + '%' AND col5 LIKE '%' + @Param1 + '%'

I thought that the above statement would work no matter what and pull all values for @ID or @Param1 if the user did not enter a value for them.  But it does not work.  There has to be a value entered for both parameters for the query to work.  So I was just wondering how I could use an IF statement to check and see if each parameter has a value and then pull absed on which parameters have a value
0
zintech
Asked:
zintech
  • 5
  • 4
  • 2
  • +3
1 Solution
 
DanishCoderCommented:
Hi zintech,

Just a thought when you are using statements:
Are you sure that there are values in the database for all of the fields used in your WHERE-clause (col3 and col5)?

I'm not sure if this is an ASP-script, but if it is, and you get a 80020009 error without further information, that's the reason ;-)

/DanishCoder
0
 
jimyXCommented:
Try case statement:
SELECT col1, col2, col3, col4, col5 FROM table1 WHERE col3 LIKE '%' + (case @ID when NULL then '_' else @ID end) + '%' AND col5 LIKE '%' + (case @Param1 when NULL then '_' else @Param1 end) + '%'
0
 
jimyXCommented:
'_' will require one character to be present in the filed to be counted so I guess that is not what you want, so ignore my last post.

I am not sure if this will work but you may try it:
SELECT col1, col2, col3, col4, col5 FROM table1 WHERE col3 LIKE (case @ID when NULL then '%' else '%'+@ID+'%' end) AND col5 LIKE (case @Param1 when NULL then '%' else '%'+@Param1+'%' end)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
deightonCommented:
have you got nulls in the variables?

try

SELECT col1, col2, col3, col4, col5 FROM table1 WHERE col3 LIKE '%' + COALESCE(@ID,'') + '%' AND col5 LIKE '%' +COALESCE(@Param1,'') + '%'
0
 
zintechAuthor Commented:
My value is coming from a textbox.  When there is nothing in the textbox, the value is ''.  Basically a blank value.  I do not believe it is null.
0
 
jimyXCommented:
If the value is '' then:
SELECT col1, col2, col3, col4, col5 FROM table1 WHERE col3 LIKE (case @ID when '' then '%' else '%'+@ID+'%' end) AND col5 LIKE (case @Param1 when '' then '%' else '%'+@Param1+'%' end)
0
 
jimyXCommented:
The problem if you filter the records by using [col3 LIKE '%' + @ID + '%'] is when @ID equals to '' then the value of the filter becomes '%%' which means you are looking for the character '%' instead of the wildcard. Because when you use '%%' it means you are disabling the wildcard and using the percentage symbol as character.
0
 
SJCFL-AdminCommented:
I see cased statements a lot when I am trying to performance tune a poorly performing query.  And the problem is not directly related to the case statement.  It is more indirect.  The current version of the optimizer does not create multiple plans for the cased statement.  And if you have indexes on the fields entered, it would be pretty obvious to you how you would want the tables accessed based on which combination of keys were entered.  But if you put the cased statements in a stored procedure, the optimizer will pick one access method.

If , however, you use IF-THEN logic, the optimizer will create a plan for each section ending up with multiple plans that match what you would naturally expect.  With 3 fields (assuming all 3 have are indexed), I would use IF-THEN logic.

If there were more than 3 fields, but only 3 or less had indexes on them. i would still use IF-THEN logic for the indexed fields and case logic for the remainder.

If more than 3 fields were indexed (and of course searchable), I'd consider using dynamic logic.  but I'd make sure I accessed views instead of base tables and check input for validity to prevent injection attacks.

Basic format:


IF  LEN(@ID) > 0 and LEN(@Param1) >0
     BEGIN
         SELECT col1, col2, col3, col4, col5 FROM table1
         WHERE col3 LIKE '%' + @ID + '%' AND col5 LIKE '%' + @Param1 + '%'
     END
ELSE
     BEGIN
        IF  LEN(@ID) > 0
             BEGIN
                 SELECT col1, col2, col3, col4, col5 FROM table1
                 WHERE col3 LIKE '%' + @ID + '%'
             END
       ELSE
             BEGIN
                  IF   LEN(@Param1) >0
                        BEGIN
                            SELECT col1, col2, col3, col4, col5 FROM table1
                            WHERE  col5 LIKE '%' + @Param1 + '%'
                        END
                   ELSE ===>>> Return your error ???
             END
     END

technically, you do not need all the begin-end clauses.  I just think it helps in case you have extra logic.  For one statement a begin-end clause is not needed. But for more than one statement it is required...
0
 
DanishCoderCommented:

My value is coming from a textbox.  When there is nothing in the textbox, the value is ''.  Basically a blank value.  I do not believe it is null.

Exactly, I had the same problem earlier today. Try to fill in a value so it is not null any longer.
My sql-string did its job until I was querying something else in the database where I had a null value. That affected my WHERE-clause in a bad way.

When you add something through an INSERT-statement, it is always good to fill data into all fields in the record (if textbox is empty then fill in this value end if).
0
 
SJCFL-AdminCommented:
LEN(LTRIM@ID)   will get the size after removing leading spaces.    

You should probably get in the habit of doing a LTRIM and RTRIM on all text entered fields or you are going to have misc problems down the road.  Not to mention checking for other garbage characters.  I'd recomend having a procedure to do standard editing on all text entry fields so your system acts consistently.
0
 
Anthony PerkinsCommented:
I suspect you will find that this:
IF  LEN(@ID) > 0

Is exactly the same as this:
IF LEN(LTRIM@ID) > 0

Now if it makes you feel better to use a LTRIM() first more power to you...
0
 
SJCFL-AdminCommented:
ok. Thanks for correcting me.
0
 
SJCFL-AdminCommented:
declare @id1 char(10)
declare @id2 char(10)
declare @id3 char(10)
declare @id4 char(10)
set @id1 = 'abc'
set @id2 = ' abc'
set @id3 = ' abc '
set @id4 = ' '
select len(@id1)
select len(@id2)
select len(@id3)
select len(@id4)

Returns 3,4,4,0


Length discounts the trailing spaces, but not the leading spaces...
0
 
Anthony PerkinsCommented:
Length discounts the trailing spaces, but not the leading spaces...
You are missing the point...
0
 
SJCFL-AdminCommented:
No, I got the point.  LTRIM not needed on IF statement.

But then I NEEDED to know exactly how LEN counted in regards to blanks.  My knowledge was inaccurate and it needed to be updated.  So I decided to share the results.

I would always have a standard routine, however, to edit text input as stated earlier so that my inputs always were handled constantly.  it is just the way I am.  Using a RTRIM and LTRIM on text input is instinctive as is looking for garbage characters.

But that does not excuse my not finding out and remembering exactly how Sql handles blank leading and trailing spaces in text fields, nor how the length function would count them.

And it is important to me that as I try to help, I strive to deepen my own knowledge.  It is not just about a quick answer for this task.  It is also about making better decisions on our future projects.
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 5
  • 4
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now