March 31, 2004 at 1:54 pm
Geeeting one and all .... I am attempting to determine what factors (table size, frequency of update vs insert, etc....) influence the best method for developing a stored procedure that will either update or insert a row (if the row did not exist). I am presenting 3 options here, I believe overall option 3 works best, but wanted to see what other thinking people thought.
Thanks,
Glenn Lehman
Examples contains 1 Table (Table1) and 3 stored procedures uspOption1, uspOption2, uspOption3:
CREATE TABLE Table1
(
PK int NOT NULL,
C1 int NOT NULL)
ON [PRIMARY]
GO
CREATE PROCEDURE uspOption1
@iPK INT,
@iC1 INT
AS
DECLARE @iErrorID INTEGER
SET XACT_ABORT OFF
IF EXISTS (SELECT C1
FROM Table1
WHERE PK = @iPK)
UPDATE Table1
SET C1 = @iC1
WHERE PK = @iPK
ELSE
INSERT
INTO Table1 (PK, C1)
VALUES (@iPK, @iC1)
SELECT @iErrorID = @@ERROR
IF @iErrorID <> 0 GOTO ERROR_HANDLER
RETURN 0
ERROR_HANDLER:
RETURN -1 * @iErrorID
GO
CREATE PROCEDURE uspOption2
@iPK INT,
@iC1 INT
AS
DECLARE @iErrorID INTEGER,
@iCount INTEGER
SET XACT_ABORT OFF
SELECT @iCount = COUNT(C1)
FROM Table1
WHERE PK = @iPK
IF @iCount > 0
UPDATE Table1
SET C1 = @iC1
WHERE PK = @iPK
ELSE
INSERT
INTO Table1 (PK, C1)
VALUES (@iPK, @iC1)
SELECT @iErrorID = @@ERROR
IF @iErrorID <> 0 GOTO ERROR_HANDLER
RETURN 0
ERROR_HANDLER:
RETURN -1 * @iErrorID
GO
CREATE PROCEDURE uspOption3
@iPK INT,
@iC1 INT
AS
DECLARE @iErrorID INTEGER,
@iRowCount INTEGER
SET XACT_ABORT OFF
UPDATE Table1
SET C1 = @iC1
WHERE PK = @iPK
SELECT @iErrorID = @@ERROR, @iRowCount = @@ROWCOUNT
IF @iErrorID <> 0 GOTO ERROR_HANDLER
IF @iRowCount = 0
BEGIN
INSERT
INTO Table1 (PK, C1)
VALUES (@iPK, @iC1)
SELECT @iErrorID = @@ERROR
IF @iErrorID <> 0 GOTO ERROR_HANDLER
END
RETURN 0
ERROR_HANDLER:
RETURN -1 * @iErrorID
GO
March 31, 2004 at 3:44 pm
Personally I prefer to use multipls SPs in groups with chekcing logic in first, update in another and insert in yet another so I get an execution plan for each circumstance if I am doing something liek you have.
Ex.
CREATE PROCEDURE dbo.usp_UpdateInsert;1
@iPK INT,
@iC1 INT
AS
SET NOCOUNT ON
IF EXISTS (SELECT C1 FROM dbo.Table1 WHERE PK = @iPK)
EXEC dbo.usp_UpdateInsert;2 @iPK, @iC1
ELSE
EXEC dbo.usp_UpdateInsert;3 @iPK, @iC1
GO
-- Does Update
CREATE PROCEDURE dbo.usp_UpdateInsert;2
@iPK INT,
@iC1 INT
AS
SET NOCOUNT ON
UPDATE dbo.Table1 SET C1 = @iC1 WHERE PK = @iPK
GO
-- Performs Insert
CREATE PROCEDURE dbo.usp_UpdateInsert;3
@iPK INT,
@iC1 INT
AS
SET NOCOUNT ON
INSERT dbo.Table1 (PK, C1) VALUES (@iPK, @iC1)
GO
Of course I played with no error handling here for simplicity on my part. You may or may not want to add any yourself.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply