[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 377
  • Last Modified:

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
0
jsuanque
Asked:
jsuanque
  • 2
2 Solutions
 
micropc1Commented:
Try...

CREATE PROCEDURE [dbo].[SP_01]
@RecordID INT,
@Desc VARCHAR(MAX)
AS 
BEGIN
   IF  (SELECT COUNT(*) FROM [dbo].[Table_1] WHERE [RecordID] = @RecordID) = 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
   ELSE
      UPDATE  [dbo].[Table_1] SET Col2 = @Desc WHERE Col1 = @RecordID
END

GO

Open in new window

0
 
jsuanqueAuthor Commented:
Sorry disregard...found the solution...

CREATE PROCEDURE [dbo].[SP_01]
@RecordID INT,
@Desc VARCHAR(MAX)
AS
IF
(SELECT COUNT(*) FROM [dbo].[Table_1] WHERE [RecordID] = @RecordID) = 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

IF
(SELECT COUNT(*) FROM [dbo].[Table_1] WHERE [RecordID] = @RecordID) <> 0
BEGIN
UPDATE  [dbo].[Table_1] SET Col2 = @Desc WHERE Col1 = @RecordID
END

GO
0
 
Anthony PerkinsCommented:
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

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

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now