The following stored procedure is supposed to add a new record to the database OR update Mdescript if it already exists. However I keep getting errors such as primary key violations. Please take a look, many thanks :)
USE [COSHH2008]
GO
/****** Object: StoredProcedure [dbo].[CHSP_OR_ACTIVITYAREA_001] Script Date: 12/02/2008 13:59:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CHSP_OU_METHOD_001] @Method VARCHAR(50), @Mdescript VARCHAR(MAX)
AS
DECLARE @rc integer
set @rc =
( SELECT @@ROWCOUNT
FROM Method as a
--
WHERE a.Method = @Method AND a.Mdescript = @Mdescript)
IF @rc IS NULL
BEGIN
--SET @rc = 0
INSERT INTO Method VALUES (@Method, @Mdescript)
END
DECLARE @mc integer
SET @mc =
(SELECT @@ROWCOUNT
FROM Method as m
WHERE m.METHOD = @Method)
IF @mc = 1
BEGIN
UPDATE Method
SET Mdescript = @Mdescript
WHERE Method = @Method
END