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

[iburyak]

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.

[iburyak]

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]

[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(@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]

[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(@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

[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 @bMoreOrLessTha nAge

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,
@bMoreOrLessTha nAge int

select * from TestTable
where nAge between case when nAge is NULL then 0
when @bMoreOrLessTha nAge = 0 then @nAge
when @bMoreOrLessTha nAge = 1 then 0
end
and case when nAge is NULL then 200
when @bMoreOrLessTha nAge = 0 then 200
when @bMoreOrLessTha nAge = 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. @bMoreOrLessTha nAge = 0 you select nAge between @nAge and 200 which is basically >
3. @bMoreOrLessTha nAge = 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.