stuck again, if else after where clause

[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.DoSearc h(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(@Variabl e1,'') = '' then '%' else @Variable1 end
and column2 like case when isnull(@Variabl e2,'') = '' 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(@Address 1,'') = '' 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(@Birthda te,'') = '' 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(@A ddress1),'') = '' THEN '%' ELSE @Address1 END AND
City LIKE CASE WHEN isnull(RTRIM(@A ddress1),'') = '' THEN '%' ELSE @City END AND
State LIKE CASE WHEN isnull(RTRIM(@A ddress1),'') = '' THEN '%' ELSE @State END AND
Zip LIKE CASE WHEN isnull(RTRIM(@A ddress1),'') = '' THEN '%' ELSE @Zip END AND
Birthdate LIKE CASE WHEN isnull(RTRIM(@A ddress1),'') = '' 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.