Changing select variable based on if statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Changing select variable based on if statement

Author

Topic

JustCharlie
Starting Member

2 Posts

Posted - 2012-02-06 : 15:23:07

I have a problem getting a comment to appear in my data set from a select. Here is a simple example of what I want:

Declare @Comment varchar(30)
set @Comment = ' '

select a.value1, b.value2, @Comment
from table1 a, table2 b
where
if value1 = value2 @comment='match'
if value1 < value2 @comment='less'
if value1 > value2 @comment='GreatEqual'

I have tried doing case in where and select clauses, and using temp tables, with no luck. I could not find anything on the boards that worked this way. I feel there should be a simple way to do this; but can't see it. This select would pull an average of 7000 rows.

Charlie

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-06 : 15:27:58

[code]
select a.value1, b.value2,
case
when value1 = value2 then 'match'
when value1 < value2 then 'less'
when value1 > value2 then 'GreatEqual'
end
from table1 a, table2 b
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-02-06 : 15:28:33

Sounds like you want a CASE expression. Maybe this will help:

select 
    a.value1, 
    b.value2, 
    case
        when a.value1 = b.value2  then 'match'
        when a.value1 < b.value2  then 'less'
        when a.value1 > b.value2 then 'GreatEqual'
        else null
    end as comment
from table1 a, table2 b

EDIT: Too slow!

JustCharlie
Starting Member

2 Posts

Posted - 2012-02-06 : 15:48:42

That worked great, thanks a lot. It was simple, I was trying to make it too complex.

Thanks again for your fast and excellent respones!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-06 : 16:03:54

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources