If then else not processing...

[jimny]

I have the following code...

IF
EXISTS (SELECT Address.AD_ID FROM Address WHERE Addr_ID = '319' AND AD_ID = '1')
THEN
UPDATE Address SET Street = 'Street1',City = 'City1',ST = 'ST1',ZIP = 'ZIP1',Phone = 'Phone1'
WHERE Addr_ID = '319' and Ad_ID = '1';
ELSE
INSERT INTO Address(Addr_ID,Street,City,ST,ZIP,Phone,Ad_ID)
VALUES('319','Street1','City1','ST1','ZIP1','Phone1','1');
END IF

and am getting...

Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'ELSE'.
Server: Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'END'.

returned in query analyzer. I have played with the structure and still get the same results. What am I missing?

And if someone responds is there a way to validate whether an update was successful in ASP like...

var = conn.execute(sql)
if var = -1 then
NG
else
Good
end if

or to return a variable from SQL

thanks for either or...

 

[ClaireHCM]

IF EXISTS (SELECT Address.AD_ID FROM Address WHERE Addr_ID = '319' AND AD_ID = '1')
begin
UPDATE Address SET Street = 'Street1',City = 'City1',ST = 'ST1',ZIP = 'ZIP1',Phone = 'Phone1'
WHERE Addr_ID = '319' and Ad_ID = '1'
end

ELSE
begin
INSERT INTO Address (Addr_ID,Street,City,ST,ZIP,Phone,Ad_ID) VALUES '319','Street1','City1','ST1','ZIP1','Phone1','1')
END

Every if condition,once condition matches,should started with 'begin' and finished with 'end'

 

[jimny]

thanx claireHsu that was expedicious

 

[JamesLean]

1) There's no such thing as THEN and ENDIF in SQL. Your query would be:

IF EXISTS (SELECT AD_ID FROM Address WHERE Addr_ID = '319' AND AD_ID = '1')
  UPDATE Address
  SET Street = 'Street1',
    City = 'City1',
    ST = 'ST1',
    ZIP = 'ZIP1',
    Phone = 'Phone1'
  WHERE Addr_ID = '319' and Ad_ID = '1'
ELSE
  INSERT INTO Address (Addr_ID, Street, City, ST, ZIP, Phone, Ad_ID)
  VALUES ('319', 'Street1', 'City1', 'ST1', 'ZIP1', 'Phone1', '1')

If you have more than one statement you need to execute in the IF/ELSE clauses then you need to use BEGIN...END like this:

IF (condition)
BEGIN
  INSERT...

  SELECT...
END
ELSE
BEGIN
  UPDATE...

  SELECT...
END

2) To check for errors use the @@ERROR function:

UPDATE t
SET c = 'val'

IF @@ERROR <> 0
  PRINT 'An error occured'

3) Variables are declared like this:

DECLARE myvar int

SET myvar = 123

--James

 

[jimny]

and thank you James for the clarification and response to part 2

 

[JamesLean]

Sorry, variables should start with an @:

DECLARE @myvar int

SET @myvar = 123

--James

 

[SQLSister]

Just as a suggestion, I always use the Begin and End even if the if condition is only one line because it makes the code easier to maintain.

 

[nikhilparchure]

I agree with SQL Sister....
Always use a Begin End for if-else gives us greater managebility and clarity