asked on

Stored Procedure with two insert statements

Hello Experts,
Was trying to create a stored procedure which will insert records on two tables (table 1 & table 2) if recordID does not exists else just update one table (table 1). Unfortunately i'm experiencing an incorrect syntax near the keyword 'ELSE' when i create the SP.

CREATE PROCEDURE [dbo].[SP_01]
@RecordID INT,
@Desc VARCHAR(MAX)
AS
BEGIN
IF
(SELECT COUNT(*) FROM [dbo].[Table_1] WHERE [RecordID] = @RecordID) = 0

INSERT INTO [dbo].[Table_1] (Col1, Col2) VALUES (@RecordID, @Desc)

INSERT INTO [dbo].[Table_2] (Col1, Col2, Col3) VALUES (@RecordID, 1, GETDATE())

ELSE
UPDATE [dbo].[Table_1] SET Col2 = @Desc WHERE Col1 = @RecordID

END

GO

ASKER CERTIFIED SOLUTION

Chris Ashcraft

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

jsuanque

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

Or simply (just fix the obvious typo in xUPDATE):

xUPDATE  [dbo].[Table_1] SET Col2 = @Desc WHERE Col1 = @RecordID
IF @@ROWCOUNT = 0
	BEGIN
		INSERT INTO [dbo].[Table_1] (Col1, Col2) VALUES (@RecordID, @Desc)
		INSERT INTO  [dbo].[Table_2] (Col1, Col2, Col3) VALUES (@RecordID, 1, GETDATE())
	END

Open in new window

jsuanque

ASKER

Thanks both works...but i like yours better:-)