compare address with address - SQL Server Forums

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)

compare address with address

Author

Topic

iloveorangesoda
Starting Member

30 Posts

Posted - 2004-09-21 : 09:42:40

Hi
I want to check one address against another. The code below works fine but the problem I am having is if all of @SAddr1,2,3 lines are blank or null and the same for the other addresses I don't want them to execute the exception.

IF  @SAddr1 != @addrA or @SAddr2 != @addrB or @SAddr3 != @addrC or @SPostcode != @postcode

Basically I only want the next part of the code to execute if there is code in all addresses. I don't know how to code for this?

 EXecute exception....etc

sorry if this doesn't make sense as I am in a hurry
I really need this asap

Thanks in advance

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-09-21 : 09:57:17

How about adding something like
AND ISNULL(@SAddr1 + @SAddr2 + @SAddr3, '') <> ''

Raymond

iloveorangesoda
Starting Member

30 Posts

Posted - 2004-09-21 : 09:59:20

Thanks

That's a great help

iloveorangesoda
Starting Member

30 Posts

Posted - 2004-09-21 : 10:17:52

AND ISNULL(@SAddr1 + @SAddr2 + @SAddr3, '') <> ''
Will this line of code set @sAddr1 ...etc to blank if they have null values, because I don't want to change any values that come in?

thanks

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-09-21 : 10:19:31

It won't assign new values to the variables, it simply adds them together and checks whether they are all null or empty (the isnull does not inherently change the value).

Raymond

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-21 : 10:25:47

quote:
Originally posted by raymondpeacock

How about adding something like
AND ISNULL(@SAddr1 + @SAddr2 + @SAddr3, '') <> ''

Raymond

If one was null, wouldn't the added value end up null (thus empty)?

Shouldn't it be:

And isnull(@SAddr1,'') + isnull(@SAddr2,'') + isnull(@SAddr3,'') <> ''

Corey

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-09-21 : 10:29:00

Yes that's right. Wasn't the requirement that code should be in all addresses? Therefore if one is null, you don't want to execute the exception.

Raymond

iloveorangesoda
Starting Member

30 Posts

Posted - 2004-09-21 : 10:37:00

Sorry it is probably me not making myself clear

If there are all blanks or all nulls in the sAddress lines or the Addr lines then no exception is to be executed. But for example if there was one part of the address then exception can be run.

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-09-21 : 10:38:16

Ah, in that case Corey's is the way to go.

Raymond

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-21 : 10:39:27

quote:
if all of @SAddr1,2,3 lines are blank or null

It sounds like it should be more like:

if isnull(@SAddr1,'') + isnull(@SAddr2,'') + isnull(@SAddr3,'')=isnull(@SAddrA,'') + isnull(@SAddrB,'') + isnull(@SAddrC,'')

Maybe soda could let us know what he meant??

Corey

iloveorangesoda
Starting Member

30 Posts

Posted - 2004-09-21 : 10:52:56

I will try and explain better with this example below is 2 rows from database:

@SAddr1 @SAddr2 @SAddr3 @addA @addB @addC
null null blank blank null null

@SAddr1 @SAddr2 @SAddr3 @addA @addB @addC
null null blank 1 High null null

The first row should not be allowed to step into the exception part of code, the second should step into the exception part of code as it has some details.

SELECT @SAddr1 = f_address_line1,
				@SAddr2 = f_address_line2,
				@SAddr3 = f_address_line3,
				
				FROM Employee
				WHERE Employee.id = @employee_id


			IF  @SwanAddr1 != @addr1 or @SwanAddr2 != @addr2 or @SwanAddr3 != @addr3 or @SwanPostcode != @f_localpostcode
			
						

			BEGIN
				
EXEC @RCode = Exceptions @f_message_type = 3...etc

END,

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-21 : 10:54:42

So then this should be more suited to you:

if (isnull(@SAddr1,'')+isnull(@SAddr2,'')+isnull(@SAddr3,'')) <> (isnull(@AddA,'')+isnull(@AddB,'')+isnull(@AddC,''))

Corey

iloveorangesoda
Starting Member

30 Posts

Posted - 2004-09-21 : 10:58:03

Thanks

iloveorangesoda
Starting Member

30 Posts

Posted - 2004-09-21 : 11:59:58

I was just wondering if you could explain what the code is actually doing as I am new to programming and would like to understand what is happening?

Is it basically saying if there are nulls or blanks exclude them or does it select all rows where there are all blanks or nulls etc.


if (isnull(@SAddr1,'')+isnull(@SAddr2,'')+isnull(@SAddr3,'')) <> (isnull(@AddA,'')+isnull(@AddB,'')+isnull(@AddC,''))

Thanks again for your time its much appreciated

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-21 : 12:42:16

Well there is no select for that particular line, so it isn't 'selecting' anything. The only thing that this does is compare a set of variables to another set of variables. If the condition is true then do 'thing A' otherwise don't do 'thing A'

if (condition=true)
Begin
thing A
End

The condition I gave you compares the 1st set of variables (@Saddr1, @Saddr2, @Saddr3) to the 2nd set of variables (@AddA, @AddB, @AddC)

So it works out like this:


@SAddr1 @SAddr2 @SAddr3 @AddA   @AddB   @AddC      Condition
------- ------- ------- ------- ------- -------    -----------
null    null    ''      null    null    'A'        ''<>'A'       True
'A'     'B'     'C'     'A'     'B'     'A'        'ABC'<>'ABA'  True
'A'     null    'C'     'A'     null    'C'        'AC'<>'AC'    False


In the event both sets are null or blank, you may also need to add an empty string check:

[code]
if ((isnull(@SAddr1,'')+isnull(@SAddr2,'')+isnull(@SAddr3,'')) <> (isnull(@AddA,'')+isnull(@AddB,'')+isnull(@AddC,'')) and (isnull(@SAddr1,'')+isnull(@SAddr2,'')+isnull(@SAddr3,''))<>'')
Begin
yadayada...
End

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources