Add or Update stored procedure

[eddy556]

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

[Traicey]

Primary key violation usually means that U are trying to enter or insert a value that already exist in the primary key field, there is nothing wrong I see in the code, Primary keys have to be unique

[eddy556]

Yes I know that, hence if it already exists it should do an update! Not a replace.

[Traicey]

Well on that case you need to have an else statement

IF @rc IS NULL
   BEGIN
  --SET @rc = 0
    INSERT INTO Method VALUES (@Method, @Mdescript)
         SELECT @@IDENTITY
    END
    ELSE
       UPDATE YOUR RECORD
       SELECT @RC

[eddy556]

Hi thanks for that, but it turned out the whole of the code can be simplified using IF EXISTS, arghh this is so obvious when I think about it. Sorry about the newbie post everyone