IF else stored procedure - 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 stored procedure

Author

Topic

ronin2307
Posting Yak Master

126 Posts

Posted - 2007-07-23 : 14:09:46

I am trying to do the following but I am not sure it is possible:

IF
(try INSERT statement)
Else 'if insert returns an error
(run UPDATE statement)

how do i write this? the error would be violation of the primary key constraint

Thanx

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-23 : 14:14:25

[code]
IF NOT EXISTS( SELECT * FROM yourTable WHERE <Condition>)
BEGIN
INSERT INTO ...
ENd
ELSe
BEGIN
UPDATE Table
SET ...
END
[/code]

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

ronin2307
Posting Yak Master

126 Posts

Posted - 2007-07-23 : 14:18:47

simple enough... thanx

ronin2307
Posting Yak Master

126 Posts

Posted - 2007-07-23 : 14:32:12

what am i missing

IF NOT EXISTS (Select * from bom_steel where fpart = @fpart)
		BEGIN
			INSERT INTO bom_steel 
			VALUES (@fPart,@fL,@fH,@fW,@fDesc)
		SET
	ELSE
		BEGIN
			UPDATE bom_steel 
			SET fl=@fl, fh=@fH, fW=@fW 
			where fpart=@fpart
		SET

Msg 156, Level 15, State 1, Procedure bom_UpIn, Line 25
Incorrect syntax near the keyword 'ELSE'.
Msg 170, Level 15, State 1, Procedure bom_UpIn, Line 31
Line 31: Incorrect syntax near 'END'.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-23 : 14:34:30

You need to END the BEGIN not SET it.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-23 : 14:35:11

quote:
Originally posted by ronin2307

what am i missing

IF NOT EXISTS (Select * from bom_steel where fpart = @fpart)
BEGIN
INSERT INTO bom_steel
VALUES (@fPart,@fL,@fH,@fW,@fDesc)
~~SET~~ END
ELSE
BEGIN
UPDATE bom_steel
SET fl=@fl, fh=@fH, fW=@fW
where fpart=@fpart
~~SET~~ END

Msg 156, Level 15, State 1, Procedure bom_UpIn, Line 25
Incorrect syntax near the keyword 'ELSE'.
Msg 170, Level 15, State 1, Procedure bom_UpIn, Line 31
Line 31: Incorrect syntax near 'END'.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

ronin2307
Posting Yak Master

126 Posts

Posted - 2007-07-23 : 14:37:43

stupid....:-(
thanx

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-07-23 : 14:37:44

[code]IF NOT EXISTS (Select * from bom_steel where fpart = @fpart)
BEGIN
INSERT INTO bom_steel
VALUES (@fPart,@fL,@fH,@fW,@fDesc)
~~SET~~END
ELSE
BEGIN
UPDATE bom_steel
SET fl=@fl, fh=@fH, fW=@fW
where fpart=@fpart
~~SET~~END[/code]

Alteratly you can use this syntax:[code]UPDATE bom_steel
SET fl=@fl, fh=@fH, fW=@fW
where fpart=@fpart

IF @@ROWCOUNT = 0
BEGIN
INSERT INTO bom_steel
VALUES (@fPart,@fL,@fH,@fW,@fDesc)

SET @fPart = SCOPE_IDENTITY()
END
[/code]

lucibra881
Starting Member

1 Post

Posted - 2010-01-18 : 04:26:24

somebody can tell me what i'm missing

The error message is : *(Msg 156, Level 15, State 1, Procedure sp_UpdateTest, Line 29
Incorrect syntax near the keyword 'END'.)

IF not exists(select Num from TABLENAME where Num = @Num) and
exists(select Num from banks where Num = @Num)
BEGIN
insert into TABLENAME (adr_num)
END*

ELSE
BEGIN
delete from TABLENAME where Num = @Num
END

Luci

Kristen
Test

22859 Posts

Posted - 2010-01-18 : 04:41:47

insert into TABLENAME (adr_num) VALUES(...)
or
insert into TABLENAME (adr_num) SELECT adr_num FROM ...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-18 : 05:22:55

Also you dont need * after END

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-01-18 : 05:47:12

I assumed that was to indicate that was Line 29 - maybe not though!