473,889 Members | 1,558 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

stuck again, if else after where clause

17 New Member
So thanks to another member here I got my view sorted out :D but now that I've begun to write my stored proc I've ran into another problem!

Basically, my stored proc takes about 30 variables and I want to build a WHERE CLAUSE constraint upon these variables. Only thing is, I would like to check the variable values before adding them to the WHERE clause and if the value of the variable is null or "" then I would like to omit the constraint from the Clause.

Here's a simple example of what I'd like to do:

Select * from TestTable

Where

If (@Variable1 is not null AND @Variable1 <> "")
begin
Column1FromTest Table = @Variable1
end

If (@Variable2 is not null AND @Variable2 <> "")
begin
AND Column2FromTest Table = @Variable2
end

etc....


Is something like this possible? Because I've been looking at Books Online for MSSQL 2k5 and searched the web without any conclusive answers.

Many thanks

Davinski
Mar 27 '07 #1
28 7211
iburyak
1,017 Recognized Expert Top Contributor
Try this:


select *
from TestTable
where column1 like case when isnull(@Variabl e1,'') = '' then '%' else @Variable1 end
and column2 like case when isnull(@Variabl e2,'') = '' then '%' else @Variable1 end
and .....
This way if Variable is null or '' will be replaced with % which basically selects all from this column.

Good Luck

Irina.
Mar 27 '07 #2
iburyak
1,017 Recognized Expert Top Contributor
Correction


[PHP]select *
from TestTable
where column1 like case when isnull(@Variabl e1,'') = '' then '%' else @Variable1 end
and column2 like case when isnull(@Variabl e2,'') = '' then '%' else @Variable2 end
and ..... [/PHP]
Mar 27 '07 #3
davinski
17 New Member
Hello,

Thanks for the prompt reply and answer. Good news is that when I use your syntax template in my stored proc I don't get any error messages, only thing is that I can't test it because I don't have any data in my table yet :S

I've been taking another look at Books Online to make sense of your syntax however I get a little confused.

I think I understand

... where column1 like (uses the like to compare, this is the left side)
case (start use of case statement)
when isnull(@Variabl e1,'') = '' (isnull converts Variable1 to '' if @Variable1 is null' then it is compared with '' to go to)
then '%' (I don't understand this part)
else @Variable1
end

So what I'm not understanding clearly is the comparison of using like with column1 and '%'

I'm not sure what this does. Can someone explain please?

Also, what is the implications when using the like command with nvarchars, will the above syntax still hold true and do what I require?

Many thanks again

Davinski




Correction


[PHP]select *
from TestTable
where column1 like case when isnull(@Variabl e1,'') = '' then '%' else @Variable1 end
and column2 like case when isnull(@Variabl e2,'') = '' then '%' else @Variable2 end
and ..... [/PHP]
Mar 27 '07 #4
iburyak
1,017 Recognized Expert Top Contributor
OK
Like is comparison operator like “=” or “>” or “<”.
When you can use like you can use wild cards instead of actual values.

Try these examples:

[PHP]select * from sysobjects where name like '%' order by name

-- above is the same as because % means everything
select * from sysobjects

-- next means I want all names that start with sys
select * from sysobjects where name like 'sys%' order by name[/PHP]


1 case (start use of case statement)
2 when isnull(@Variabl e1,'') = '' --(isnull converts Variable1 to '' if @Variable1 is null' then it is compared with '' to go to)
3 then '%' --(I don't understand this part)
4 else @Variable1
5 end
So in line 2 I check if variable is null then I convert it to '' if it is '' then it is already '' and will be as is
in both cases null or '' they will be converted to '' and compared if it is true or not.
If it is TRUE and variable is null or '' we go to line 3
Here I convert result to % meaning give me everything from this column no conditions.
If it is FALSE we go to line 4 and get what is in a variable no wild cards involved.



To test me do following:

[PHP]-- 1. Create test proc
Create proc GetObjectName
@Name varchar(30) = Null
AS

select * from sysobjects
where name like case when isnull(@Name, '') = '' then '%' else @Name end

-- 2. Execute proc with different parameters and see results.
EXEC GetObjectName
EXEC GetObjectName Null
EXEC GetObjectName ''
EXEC GetObjectName 'syscolumns'
EXEC GetObjectName 'sys%'[/PHP]

Good Luck
Mar 27 '07 #5
davinski
17 New Member
Thank you so much for the explanation, it was enlightening and you do truely know what you are doing!

In light to understand your methods more completely, I also created a Test Database with 1 table.

What I found was that the 'like' comparison doesn't hold true for int, or should I say that when the column definition is an 'int' and the variable is passed through as null, the int column definition gets compared with '%' which I believe is where my Query Editor is reporting an error.

Would it be possible for you to modify your template to accomodate for 'int' and 'bit' column definitions?

Many thanks

Davinski
Mar 28 '07 #6
iburyak
1,017 Recognized Expert Top Contributor
I don't think this is a problem.

To test me execute following statements:


[PHP]select * from sysobjects where id like '%' -- id is int
select * from syscomments where encrypted like '%' -- encrypted is bit[/PHP]
Mar 28 '07 #7
davinski
17 New Member
Right you are iburyak!!!

using like '%' on int and encrypted bits works just fine and it's great, thank you.

I have however stumbled upon a small problem with your template that maybe you can solve, that is in using your template I tried to do the following, nAge is of int definition.

Expand|Select|Wrap|Line Numbers
  1. nAge like 
  2.     case 
  3.     when isnull(@nAge,'') = '' 
  4.         then '%' 
  5.         else @nAge 
  6.     end
  7.  
This works fine if the variable @nAge is NULL, however if @nAge is in integer value, I receive an error.

Any ideas? I think it might have something to do with the like comparison on integers?

Thanks

Davinski



I don't think this is a problem.

To test me execute following statements:


[PHP]select * from sysobjects where id like '%' -- id is int
select * from syscomments where encrypted like '%' -- encrypted is bit[/PHP]
Mar 28 '07 #8
iburyak
1,017 Recognized Expert Top Contributor
Unfortunately I can't reproduce your error. It is possible that my server version handles it differently...

But I have a hunch on what might happen.
Statement below returns mixed datatypes in True case it returns character datatype and in False it is integer.

[PHP]case
when isnull(@nAge,'' ) = ''
then '%'
else @nAge
end[/PHP]

Try to do following:
[PHP]nAge like
case
when isnull(@nAge,'' ) = ''
then '%'
else cast(@nAge AS varchar(20))
end[/PHP]

Show me full text of error message if any.

Thank you.
Mar 28 '07 #9
davinski
17 New Member
iburyak, many thanks, I can see how your cast will work however I can't test it right now because I've created another error :S btw the error before was something along the lines of

Expand|Select|Wrap|Line Numbers
  1. Conversion failed when converting the varchar value '%' to data type int.
  2.  
I'll let you know how I get on with your CAST, however one other little modification to your template if you could as that is if I wanted to test for

Expand|Select|Wrap|Line Numbers
  1. nAge > @nAge
  2.  
based on an input Variable for example @bMoreOrLessTha nAge

in psuedo I'd like

Expand|Select|Wrap|Line Numbers
  1. @nAge int,
  2. @bMoreOrLessThanAge int
  3.  
  4. select * from TestTable 
  5.  
  6. Where 
  7.  
  8. if nAge is NOT NULL
  9. begin
  10. if @bMoreOrLessThanAge = 0
  11.  
  12. nAge > @nAge
  13.  
  14. else if @bMoreOrLessThanAge = 1
  15.  
  16. nAge < @nAge
  17.  
  18. end
  19.  
  20.  

I hope you understand my scribbling, and hope you've got some suggestions?

Thanks again

Davinski
Mar 28 '07 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

33
3889
by: Diez B. Roggisch | last post by:
Hi, today I rummaged through the language spec to see whats in the for ... else: for me. I was sort of disappointed to learn that the else clauses simply gets executed after the loop-body - regardless of the loop beeing entered or not. So where is an actual use case for that feature? I imagined that the else-clause would only be executed if the loop body
1
2364
by: Samuel Hon | last post by:
Hi I'm a bit stuck with a SELECT query. This is a simplified version of what I need. I've had a look in a few books and online but I'm definitely missing something. I'm trying to avoid looping and cursors. I'll be running this in a stored procedure on SQL 7. I have a separate query which returns a series of numbers, A, say 101 103 107 109 113.
7
26514
by: jcochran | last post by:
What would be the correct way of writing a sql select state with where clause while also using IF ELSE. I am using T-SQL and I cannot get it to work. I probably have the syntax wrong. I want to be able to have different where/and/or clauses in the sql dependant on what value was passed into the @SearchTerm parameter in this stored procedure. Can I use CASE statements in the WHERE section? Or is that strickly for SELECT statements?
3
2101
by: visionstate | last post by:
Hi All, I have used the following article to help me build a query 'on the fly': http://www.fontstuff.com/access/acctut17.htm It's a very useful article and is exactly what I was looking for to use in my current project. To give you a brief rundown I am basically creating a Training Database which would allow users to choose 1 or more options from a drop down box to select the criteria I need.
25
1884
by: metaperl.etc | last post by:
A very old thread: http://groups.google.com/group/comp.lang.python/browse_frm/thread/2c5022e2b7f05525/1542d2041257c47e?lnk=gst&q=for+else&rnum=9#1542d2041257c47e discusses the optional "else:" clause of the for statement. I'm wondering if anyone has ever found a practical use for the else branch?
5
8190
by: clear1140 | last post by:
Good day!! I was wondering if it is possible to put an if else statement inside a where clause of an sql statement... you see i would like my update statement to do this: update trial_clear set num = @count2 /* @count2 is a integer passed*/ where if (select top 1 def from trial_clear where num is NULL) is NULL def is NULL else
23
2795
by: bearophileHUGS | last post by:
So far in Python I've almost hated the 'else' of the 'for' loops: - I have problems to remember its meaning; - It gives me little problems when I later want to translate Python code to other languages (and you always have to translate long-lived code). - I have used it only once, so far. So so far I'd liked to see it removed from Python 3.0. But then this article:
4
2084
bencoding
by: bencoding | last post by:
Hello, I'm new to this board and new to T-SQL, I have a project at my work that is requiring me to modify an existing stored procedure to add more functionality. How can I use a CASE or IF...ELSE (preferably a single IF) statement for this scenario? if @report_field_value is equal to 0 then leave that where clause out completely else if it is a number greater than 0 then use the where clause of WHERE report_field_value =...
3
1763
by: BlueroY | last post by:
hi, I'm working on an exercise, i did a lot of work already and i just can't figure where I'm going wrong, this is what I'm trying to achieve Sample IO ******************************************************************************* Welcome to PeopleSoft 2 MENU: (A)dd student, (D)elete, (L)ist, (S)ort, e(X)it a Enter the student number MSXMIC001 Enter the name
0
9961
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9803
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10783
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10439
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7148
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5825
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6021
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4642
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4248
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.