IF... ELSE - 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)

IF... ELSE

Author

Topic

waxdart23
Starting Member

33 Posts

Posted - 2004-06-21 : 05:33:34

I am trying to use IF... ELSE to insert data into a table deoending on the content of the original table. The script below is supposed to insert address data into field a certain way if one of the fields is NULL, and then a different way otherwise.


Declare	
	@Customer_no varchar (15),
	@Street2 varchar (55),
	@Street1 varchar (55),
	@City varchar (18),
	@Postcode varchar (10),
	@Country varchar (5),
	@Address_Type varchar (5),
	@Phone1 varchar (20),

IF (SELECT ADDRESS2 FROM MASTER_ACCOUNT) LIKE NULL
BEGIN
	Declare Cur_Grab_data Cursor
	FOR select ACCOUNT,ADDRESS2,ADDRESS1,CITY,POSTCODE,COUNTRY,Address_Type,DAY_PHONE
	from MASTER_ACCOUNT

	OPEN Cur_Grab_data
	FETCH NEXT FROM Cur_Grab_data INTO @Customer_no, @Street2, @Street1, @City, @Postcode, @Country, @Address_Type, @Phone1

	WHILE @@FETCH_STATUS = 0

	begin
	insert into T_Address values(@Customer_no, @Street2, @Street1, @City, @Postcode, @Country, @Address_Type, @Phone1)
	FETCH NEXT FROM Cur_Grab_data INTO @Customer_no, @Street2, @Street1, @City, @Postcode, @Country, @Address_Type, @Phone1
	end

	close Cur_Grab_data
	DEALLOCATE Cur_Grab_data
END
ELSE
	Declare Cur_Grab_data Cursor
	FOR select ACCOUNT,ADDRESS1,ADDRESS2,CITY,POSTCODE,COUNTRY,Address_Type,DAY_PHONE
	from MASTER_ACCOUNT

	OPEN Cur_Grab_data
	FETCH NEXT FROM Cur_Grab_data INTO @Customer_no, @Street2, @Street1, @City, @Postcode, @Country, @Address_Type, @Phone1

	WHILE @@FETCH_STATUS = 0

	begin
	insert into T_Address values(@Customer_no, @Street2, @Street1, @City, @Postcode, @Country, @Address_Type, @Phone1)
	FETCH NEXT FROM Cur_Grab_data INTO @Customer_no, @Street2, @Street1, @City, @Postcode, @Country, @Address_Type, @Phone1
	end

	close Cur_Grab_data
	DEALLOCATE Cur_Grab_data

I am getting the following error:
Incorrect syntax near the keyword 'IF'

Can anybody help?

Thanks
P

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-06-21 : 06:17:46

try IF (SELECT ADDRESS2 FROM MASTER_ACCOUNT) IS NULL

but what if SELECT ADDRESS2 FROM MASTER_ACCOUNT return more than 1 record??

and using cursors will kill performace...

Go with the flow & have fun! Else fight the flow :)

Mr.Xyz
Starting Member

5 Posts

Posted - 2004-06-21 : 06:26:11

i think u have missed out end somewhere check it out because i dont find any flaw here

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-06-21 : 06:42:22

maybe u should put begin ... end for the ELSE part too.

ELSE
BEGIN
....
END

Go with the flow & have fun! Else fight the flow :)

waxdart23
Starting Member

33 Posts

Posted - 2004-06-21 : 07:57:28

I am writing this script for a once only conversion process. The logic I am trying to achieve is basically -

IF MASTER_ACCOUNT.address2 = VALUE
INSERT MASTER_ACCOUNT.address1 INTO T_Address.street2
INSERT MASTER_ACCOUNT.address2 INTO T_Address.street1
ELSE
IF $MASTER_ACCOUNT.address2 = NULL
INSERT MASTER_ACCOUNT.address1 INTO T_Address.street1
INSERT MASTER_ACCOUNT.address2 INTO T_Address.street2

I cannot find a way of doing this, I have tried using WHILE but as far as I can tell neither IF or WHILE seem to allow the process to loop.

Thanks
P

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-06-21 : 08:13:17

maybe this would work?

INSERT INTO T_Address.street1
select case when(MASTER_ACCOUNT.address2 = value)then T_Address.address2
when(MASTER_ACCOUNT.address2 is null)then T_Address.address1
end

INSERT INTO T_Address.street2
select case when(MASTER_ACCOUNT.address2 = value)then T_Address.address1
when(MASTER_ACCOUNT.address2 is null)then T_Address.address2
end

Go with the flow & have fun! Else fight the flow :)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-21 : 08:50:22

or you could do:

Insert Into T_Address
Select
Street1 = isnull(Address2,Address1),
Street2 = case when Address2 is not null then Address1 else Address2 end

Corey

waxdart23
Starting Member

33 Posts

Posted - 2004-06-21 : 09:52:45

The CASE statement did the trick, thanks for everyones help.

P.

Thanks
P

jkiley
Starting Member

1 Post

Posted - 2004-07-01 : 11:19:01

in your DECLARE statement...your last entry has a comma after it but preceding the IF statement

remove the comma =)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources