stuck again, if else after where clause

[davinski]

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
Column1FromTestTable = @Variable1
end

If (@Variable2 is not null AND @Variable2 <> "")
begin
AND Column2FromTestTable = @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

[iburyak]

Try this:

select *
from TestTable
where column1 like case when isnull(@Variable1,'') = '' then '%' else @Variable1 end
and column2 like case when isnull(@Variable2,'') = '' 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.

[iburyak]

Correction


[PHP]select *
from TestTable
where column1 like case when isnull(@Variable1,'') = '' then '%' else @Variable1 end
and column2 like case when isnull(@Variable2,'') = '' then '%' else @Variable2 end
and ..... [/PHP]

[davinski]

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(@Variable1,'') = '' (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(@Variable1,'') = '' then '%' else @Variable1 end
and column2 like case when isnull(@Variable2,'') = '' then '%' else @Variable2 end
and ..... [/PHP]

[iburyak]

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(@Variable1,'') = '' --(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

[davinski]

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

[iburyak]

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]

[davinski]

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.

nAge like 
    case 
    when isnull(@nAge,'') = '' 
        then '%' 
        else @nAge 
    end
 

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]

[iburyak]

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.

[davinski]

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

Conversion failed when converting the varchar value '%' to data type int.
 

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

nAge > @nAge
 

based on an input Variable for example @bMoreOrLessThanAge

in psuedo I'd like

@nAge int,
@bMoreOrLessThanAge int
 
select * from TestTable 
 
Where 
 
if nAge is NOT NULL
begin
if @bMoreOrLessThanAge = 0
 
nAge > @nAge
 
else if @bMoreOrLessThanAge = 1
 
nAge < @nAge
 
end
 
 

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

Thanks again

Davinski

[iburyak]

Try this:

[PHP]@nAge int,
@bMoreOrLessThanAge int

select * from TestTable
where nAge between case when nAge is NULL then 0
when @bMoreOrLessThanAge = 0 then @nAge
when @bMoreOrLessThanAge = 1 then 0
end
and case when nAge is NULL then 200
when @bMoreOrLessThanAge = 0 then 200
when @bMoreOrLessThanAge = 1 then @nAge
end[/PHP]

In this case when
1. nAge is NULL you select nAge between 0 and 200 which basically all available valid age values
2. @bMoreOrLessThanAge = 0 you select nAge between @nAge and 200 which is basically >
3. @bMoreOrLessThanAge = 1 you select nAge between 0 and nAge which is basically <

[davinski]

Hey, that last example works great, amazing!

Can you help me find out what the problem is with this snippet of your template?

 
nAge like 
    case 
    when isnull(@nAge,'') = '' 
    then '%'
    else @nAge
    end
 

When I run the stored proc and @nAge is NULL I get this error

 
Conversion failed when converting the varchar value '%' to data type int.
 
 

Can you help me understand why I'm getting this error?

Thanks again

Davinski

[iburyak]

Did you try my previous suggestion?

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

[davinski]

iburyak,

Yes I've tried your previous suggestion with absolute success :D

I just don't understand the cryptic message when I don't use the CAST

Conversion failed when converting the varchar value '%' to data type int.
 

I think that this message does not relate to this line does it?

then '%'
 

Thanks for all of your help

Davinski

Did you try my previous suggestion?

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

[iburyak]

Message does relate to this line but you should ignore it if it all works.
You have to understand that on the left side you have integer and on the right side you have character data that is converted to int according to situation if possible. In our case it is possible. Why I had to convert @nAge to character datatype because ‘%’ is a character and parser wants to be consistent and always return the same datatype on the right side of equation.

Try all possible parameters to make sure everything works.

I do believe you need to do some upgrade or latest service pack on SQL Server.
I used to have errors like that but not any more.

My server is constantly upgraded to latest Service packs by DBA team.

Good Luck.

[davinski]

Wow, do you ever sleep? you're like a machine!!!

Just one last anomaly I think, and that is when I use your template on a column in the table that is declared as a bool, bActive and in the stored procedure I take a variable defined as a bit, @bActive (the reasoning for it being a bit definition is to do with nettiers), when I run my stored proc, for null values and value of 1 I get the disired results, however passing the @bActive a 0 returns everything and I would just like to return the rows which are False, what am I doing wrong?

Here is some code

 
bActive is defined as a bool in the table definition.
 
 
 
 
@bActive bit,
 
select * from TestTable where
 
bActive like 
    case 
        when isnull(@bActive,'') = '' 
        then '%'
        else cast(@bActive AS char(20))
 
        end
 
 

Any ideas on why I'm not able to just retreive the rows that are false?

if I pass 1 to @bActive variable and get all the rows which are True, null and I get everything, but 0 I get everything too? that shouldn't be happenning should it?

Thanks

Davinski

Message does relate to this line but you should ignore it if it all works.
You have to understand that on the left side you have integer and on the right side you have character data that is converted to int according to situation if possible. In our case it is possible. Why I had to convert @nAge to character datatype because ‘%’ is a character and parser wants to be consistent and always return the same datatype on the right side of equation.

Try all possible parameters to make sure everything works.

I do believe you need to do some upgrade or latest service pack on SQL Server.
I used to have errors like that but not any more.

My server is constantly upgraded to latest Service packs by DBA team.

Good Luck.

[iburyak]

Change

[PHP]@bActive bit,

select * from TestTable where

bActive like
case
when isnull(@bActive,'') = ''
then '%'
else cast(@bActive AS char(20))

end[/PHP]

To

[PHP]@bActive char(1),

select * from TestTable where

bActive like
case
when isnull(@bActive,'') = ''
then '%'
else @bActive

end[/PHP]

It is just a work around. It shoud work but it looks like it converts bit to char(20) incorrectly and then it is lost at all.

[iburyak]

I am in a different time zones with you it is 8:00 PM my time... :)

[davinski]

Thanks for the work around, I'm yet to test it with CodeSmith and nettiers but I think it might cause a problem :S not sure.

Is there another work around or is the problem caused by my SQL server not being up to date?

I'm using MSSQL 2005 but it might not be patched and up to date.

Many thanks for any insight or other work arounds.

Davinski

Change

[PHP]@bActive bit,

select * from TestTable where

bActive like
case
when isnull(@bActive,'') = ''
then '%'
else cast(@bActive AS char(20))

end[/PHP]

To

[PHP]@bActive char(1),

select * from TestTable where

bActive like
case
when isnull(@bActive,'') = ''
then '%'
else @bActive

end[/PHP]

It is just a work around. It shoud work but it looks like it converts bit to char(20) incorrectly and then it is lost at all.

[iburyak]

You didn't try a solution and asking for another one....
It sounds strange. You think I made it up or just guessed it?

[davinski]

Yes I am sorry that I did not try your other solution before requesting another one however I am simply trying to cover all bases and as we all are able to use our experience from similar problems to solves new ones. And I believe I made a good judgement call with the Codesmith and Nettiers constraint on the problem I have so given forth.

I'm sure you didn't make anything up or guess your solution, but I am now confident that your last solution will not work for me. The reason why is as I've said before is that I am using Codesmith and Nettiers.

In my aspx page I will have many textboxes, many drop downs and checkboxes in a search page.

And from my .cs file on postback I would make a call something similar to

DataRepository.MyTable.DoSearch(TextBox1Value, DropDown1Value, CheckBox1Value);

Now from the above code, it will call the relevant stored procedure in my MSSQL 2k5 db, namely here DoSearch which would take in variables as

DoSearch( nvarchar(50), int, bit)

The reason why the last variable is required to be a 'bit' is because although in C# the definition of checkbox values are of type bool, the equivelent in the db is of type bit so the conversion by Enterprise Manager is automatic.

I therefore cannot have my stored proc be

DoSearch( nvarchar(50), int, char(1))

Because this conversion will simply just NOT happen.

So I only see two solutions,

1. I convert the bool to char(1) in C# which is something really rubbish before calling my stored proc.

2. Read the value as type BIT in the stored proc then do the conversion within DB which we have already seen not to work.


The other constraint on the design of the stored proc is that if a checkbox is not selected, a drop down is not selected OR a textbox is not filled, then ALL the results from the DB is returned and is only constrained by the particular fields the user selects, for example, ticks a checkbox or fills in a textbox.




This is then simply the reason why I asked you if there was something wrong with my MSSQL2k5 server or if there was another solution that from your experience you might know of and wouldn't mind sharing.

Many thanks

Davinski

You didn't try a solution and asking for another one....
It sounds strange. You think I made it up or just guessed it?

[iburyak]

I hope one of these would work:

1.
[PHP]@bActive bit,

select * from TestTable where

bActive like case when @bActive is null then '%' else cast(@bActive AS varchar(20)) end [/PHP]

2.

[PHP]@bActive bit,

select * from TestTable where

(
bActive = case when isnull(@bActive,'') = '' then 0 else @bActive end
or
bActive = case when isnull(@bActive,'') = '' then 1 else @bActive end
)[/PHP]

[iburyak]

Correction to number 2.

[PHP]
@bActive bit,

select * from TestTable where

(
bActive = case when @bActive is null then 0 else @bActive end
or
bActive = case when @bActive is null then 1 else @bActive end
)[/PHP]

Good Luck.

[davinski]

Yay, finally, this is the solution, the one below is what I've been looking for and fulfills all the conditions required.

Many thanks for all your 'Thinking' whether it was hard for you or not; it would have definately been hard for me!

Regards

Davinski

Correction to number 2.

[PHP]
@bActive bit,

select * from TestTable where

(
bActive = case when @bActive is null then 0 else @bActive end
or
bActive = case when @bActive is null then 1 else @bActive end
)[/PHP]

Good Luck.

[iburyak]

Did you try this one? It is more compact and consistent with other cases you are going to use.


[PHP]
@bActive bit,

select * from TestTable where

bActive like case when @bActive is null then '%' else cast(@bActive AS varchar(20)) end [/PHP]



@bActive is null - solved the problem.

It looks like isnull(@bActive ,'') was converting variable itself which is a bug and shouldn't happen.

[drexlin]

Correction


[PHP]select *
from TestTable
where column1 like case when isnull(@Variable1,'') = '' then '%' else @Variable1 end
and column2 like case when isnull(@Variable2,'') = '' then '%' else @Variable2 end
and ..... [/PHP]

For my current project I am in the same situation. I tried this solution and I'm not getting any results (records) from this code.

We have a user interface where a person enters in someone's personal data (ie. first name, last name, address, etc.) and the program runs the below query.

[PHP]
SELECT CustID, LName, FName, MI, Address1, Address2,
City, State, Sex, Zip, Birthdate
FROM Customers
WHERE LName LIKE '%' + @LName + '%' AND
FName LIKE @FName + '%' AND
Address1 LIKE CASE WHEN isnull(@Address1,'') = '' THEN '%' ELSE @Address1 END AND
City LIKE CASE WHEN isnull(@City,'') = '' THEN '%' ELSE @City END AND
State LIKE CASE WHEN isnull(@State,'') = '' THEN '%' ELSE @State END AND
Zip LIKE CASE WHEN isnull(@Zip,'') = '' THEN '%' ELSE @Zip END AND
Birthdate LIKE CASE WHEN isnull(@Birthdate,'') = '' THEN '%' ELSE @Birthdate END
[/PHP]

But when I run this, I get only people with blank addresses.

Any suggestions?

[iburyak]

I suspect you pass spaces somewhere.

Try this:

[PHP]
SELECT CustID, LName, FName, MI, Address1, Address2,
City, State, Sex, Zip, Birthdate
FROM Customers
WHERE LName LIKE '%' + @LName + '%' AND
FName LIKE @FName + '%' AND
Address1 LIKE CASE WHEN isnull(RTRIM(@Address1),'') = '' THEN '%' ELSE @Address1 END AND
City LIKE CASE WHEN isnull(RTRIM(@Address1),'') = '' THEN '%' ELSE @City END AND
State LIKE CASE WHEN isnull(RTRIM(@Address1),'') = '' THEN '%' ELSE @State END AND
Zip LIKE CASE WHEN isnull(RTRIM(@Address1),'') = '' THEN '%' ELSE @Zip END AND
Birthdate LIKE CASE WHEN isnull(RTRIM(@Address1),'') = '' THEN '%' ELSE @Birthdate END [/PHP]


Hope it helps.

[drexlin]

Thanks, we figured it out, though, I don't know why it would cause an issue.

State and Zip were defined as char instead of varchar. When I change the definition to varchar, it works fine.

[iburyak]

When it is a char for example char(10)

When you save something shorter then that it adds spaces for you to make column 10 characters long for example you have 6 digit zip code it will return value like this with static length:

[PHP]"123456 "[/PHP]

Varchar is variable length and takes as much space as necessary so in this code zip code will be:

"123456"

Hope my explanation is clear.

If you'll need my help in a future send me a PM.
Good Luck.