Comparing column and @variable for difference

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

Transact-SQL (2000)

Comparing column and @variable for difference

Author

Topic

Kristen
Test

22859 Posts

Posted - 2004-11-05 : 07:57:41

What's the best way to check for difference:


WHERE (MyColumn <> @MyVar 
          OR (MyColumn IS NULL AND @MyVar IS NOT NULL)
          OR (MyColumn IS NOT NULL AND @MyVar IS NULL)
      ) OR 
      (MyOtherColumn <> @MyOtherVar ...

I was trying to think of a crafty way using NullIf - but if MyColumn is null then NullIf(MyColumn, 'XXX') blows up.

Then I thought about a User Defined Function using a SQLVariant as a data type - so I could wrap the logic with something more readable:

WHERE MyTestIfEqual(MyColumn, @MyVar) = 1

but I haven't tried that yet, so then I thought of someone who would know the answer.

So what do you reckon is best?

Kristen

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-05 : 08:21:20

Well, I don't know the "best" way, but using a UDF is NOT the best way. What you have now seems fine to me. This is a little shorter and should be semantically the same:

WHERE NOT (@MyVar IS NULL OR MyColumn IS NULL OR MyColumn=@MyVar)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-11-05 : 08:24:31

Maybe this does it: (a few examples)


Declare @var1 varchar(100),
	@var2 varchar(100)

Select @var1 = 'blah', @var2 = 'test'
Select @var1, @var2, case when isnull(@var1,'xxx_')+'_'+isnull(@var1,'') <> isnull(@var2,'xxx_')+'_'+isnull(@var2,'') then 'different' else 'equal' end

Select @var1 = 'test', @var2 = 'test'
Select @var1, @var2, case when isnull(@var1,'xxx_')+'_'+isnull(@var1,'') <> isnull(@var2,'xxx_')+'_'+isnull(@var2,'') then 'different' else 'equal' end

Select @var1 = 'xxx', @var2 = null
Select @var1, @var2, case when isnull(@var1,'xxx_')+'_'+isnull(@var1,'') <> isnull(@var2,'xxx_')+'_'+isnull(@var2,'') then 'different' else 'equal' end

Select @var1 = null, @var2 = null
Select @var1, @var2, case when isnull(@var1,'xxx_')+'_'+isnull(@var1,'') <> isnull(@var2,'xxx_')+'_'+isnull(@var2,'') then 'different' else 'equal' end

Corey

Kristen
Test

22859 Posts

Posted - 2004-11-05 : 08:34:35

I had thought of using the NOT approach - but then thought it might be backwards interpretted by the next developer coming along (or me in 6 months time!)

Corey: You've got too much time on your hands!

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-05 : 08:59:24

this is a little crazy, but if you have 4 variables with different values for the columns and you want to see all rows in which ANY do not match, and be able to handle nulls, you can try a very "outside the box" approach like this:


Select 
  ID,col1,col2,col3,col4 
from
(
  Select 1 as tmp, ID, Col1, Col2, Col3, Col4
  From YourTable
  UNION ALL
  select 0 as tmp, ID, @Var1, @Var2, @Var3, @Var4
  from YourTable
) a
group by 
  id, col1,col2,col3,col4
having 
  count(*)=1 and min(tmp)=1

don't know how THAT one would perform, though, that's for sure. probably not too well. A brand new shiny nickel to the first person who can explain what it is doing !

(and you thought corey had too much time on HIS hands)

by the way -- more traditionally, I just use:


WHERE ISNULL(Col1,'**NULL**') <> ISNULL(@Var1,'**NULL**') OR
      ISNULL(Col2,-99999) <> ISNULL(@Var2,-99999) OR
      ISNULL(Col3,'1/1/1950') <> ISNULL(@Var,'1/1/1950') ....

just making sure to put in default values that will never occur, and that match the appropriate datatype.

- Jeff

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-11-05 : 09:06:24

Actually I didn't have time as I was trying to run out to a meeting... however it does work

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources