Solved

If statement for SELECT statement

Posted on 2012-04-03
15
357 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

752 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