if exists update else insert stored procedure

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

SQL Server Development (2000)

if exists update else insert stored procedure

Author

Topic

hanzzz
Starting Member

5 Posts

Posted - 2002-03-01 : 10:32:43

Hi
I have this simple insert procedure that works fine. Can anyone help me with the code to check if @mEmail_6 exists. If so the sp should do an update, if not, it should insert the record. Can't get it to work.

CREATE PROCEDURE [insert_mysp]
(@mClubID_2 [int], @mLid_3 [int],@mCrit_4[int],@mName_5[varchar](25), @mEmail_6 [varchar](25))

AS INSERT INTO Table ([mClubID],[mLid],[mCrit],[mName],[mEmail])
VALUES ( @mClubID_2,@mLid_3,@mCrit_4,@mName_5,@mEmail_6)
GO

Thanks for help!
hanzzz

nr
SQLTeam MVY

12543 Posts

Posted - 2002-03-01 : 10:54:23

if exists (select * from Table where mEmail = @mEmail_6)
update table ...
else
insert table ...

or you could

update table ...
if @@ rowcount = 0
insert table ...

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

hanzzz
Starting Member

5 Posts

Posted - 2002-03-01 : 11:48:26

Thanks for your prompt answer! My sp now looks like:

CREATE PROCEDURE [myproc]
(@mEmail_1 [varchar](25), @mClubID_3 [int], @mLid_4 [int], @mCrit_5 [int], @mName_6 [varchar](25))
AS
if exists (select * from Table where mEmail = @mEmail_1)
UPDATE Table
SET [mClubID]= @mClubID_3,[mLid]= @mLid_4,[mCrit]= @mCrit_5,[mName] = @mName_6, [mEmail] = @mEmail_1 WHERE [mEmail] = @mEmail_1
Else
INSERT INTO Table
([mClubID],[mLid], [mCrit], [mName], [mEmail])
VALUES ( @mClubID_3, @mLid_4, @mCrit_5, @mName_6, @mEmail_1)
GO

This does the job well. Is this too much code and can I do with less?

thanks

Edited by - hanzzz on 03/01/2002 12:20:44

nr
SQLTeam MVY

12543 Posts

Posted - 2002-03-01 : 12:25:03

That looks good.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources