Solved

If statement for SELECT statement

Posted on 2012-04-03
15
343 Views
Last Modified: 2012-04-18
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
Comment
Question by:zintech
  • 5
  • 4
  • 2
  • +3
15 Comments
 

Expert Comment

by:DanishCoder
ID: 37800799
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
 
LVL 24

Expert Comment

by:jimyX
ID: 37800899
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
 
LVL 24

Expert Comment

by:jimyX
ID: 37800921
'_' 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
 
LVL 18

Expert Comment

by:deighton
ID: 37800933
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
 

Author Comment

by:zintech
ID: 37801682
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
 
LVL 24

Expert Comment

by:jimyX
ID: 37801773
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
 
LVL 24

Expert Comment

by:jimyX
ID: 37801863
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 6

Expert Comment

by:SJCFL-Admin
ID: 37802818
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
 

Expert Comment

by:DanishCoder
ID: 37803630

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
 
LVL 6

Expert Comment

by:SJCFL-Admin
ID: 37805808
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37805915
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
 
LVL 6

Expert Comment

by:SJCFL-Admin
ID: 37806118
ok. Thanks for correcting me.
0
 
LVL 6

Expert Comment

by:SJCFL-Admin
ID: 37806139
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37808507
Length discounts the trailing spaces, but not the leading spaces...
You are missing the point...
0
 
LVL 6

Accepted Solution

by:
SJCFL-Admin earned 500 total points
ID: 37811803
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now