MS SQL

Add or Update stored procedure

Please support our MS SQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Jan 2008

Posts: 32

Reputation: eddy556 is an unknown quantity at this point

Solved Threads: 0

eddy556

Offline

Light Poster

Add or Update stored procedure

Dec 2nd, 2008

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

 Help with Code Tags 
 SQL Syntax (Toggle Plain Text) 
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
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

Last edited by peter_budo; Dec 3rd, 2008 at 7:16 pm. Reason: Correcting closing tag from [code] to [/code]

•

Join Date: Mar 2008

Posts: 268

Reputation: Traicey is an unknown quantity at this point

Solved Threads: 19

Traicey

Offline

Posting Whiz in Training

Re: Add or Update stored procedure

Dec 3rd, 2008

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

Some people get so rich they lose all respect for humanity. That's how rich I want to be.

•

Join Date: Jan 2008

Posts: 32

Reputation: eddy556 is an unknown quantity at this point

Solved Threads: 0

eddy556

Offline

Light Poster

Re: Add or Update stored procedure

Dec 3rd, 2008

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

•

Join Date: Mar 2008

Posts: 268

Reputation: Traicey is an unknown quantity at this point

Solved Threads: 19

Traicey

Offline

Posting Whiz in Training

Re: Add or Update stored procedure

Dec 3rd, 2008

Well on that case you need to have an else statement

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
IF @rc IS NULL
   BEGIN
  --SET @rc = 0
    INSERT INTO Method VALUES (@Method, @Mdescript)
         SELECT @@IDENTITY
    END
    ELSE
       UPDATE YOUR RECORD
       SELECT @RC
IF @rc IS NULL
   BEGIN
  --SET @rc = 0
    INSERT INTO Method VALUES (@Method, @Mdescript)
         SELECT @@IDENTITY
    END
    ELSE
       UPDATE YOUR RECORD
       SELECT @RC

Some people get so rich they lose all respect for humanity. That's how rich I want to be.

•

Join Date: Jan 2008

Posts: 32

Reputation: eddy556 is an unknown quantity at this point

Solved Threads: 0

eddy556

Offline

Light Poster

Re: Add or Update stored procedure

Dec 3rd, 2008

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