Stored procedure to delete a record

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Stored procedure to delete a record

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-06-20 : 09:42:32

Aecio writes "I have not found anyone who has the same problem I have. I have a ton of stored procedures that return values to validate their action. They all work fine and they all return the values they are supposed to return EXCEPT one. This one is unique in that it has a DELETE statement in it.

I run these procedure from my ASP pages.

When I run this one, which has a logical test to see if it can delete or not a certain record depending on whether it has associated records in another table it stops returning errors. BUT ONLY if it actually does the DELETE. If it does not go into the logical statement and does not delete, it returns the value.

What am I doing wrong?

Thanks


CREATE  PROCEDURE [dbo].SP_EXCLUI_CONTATOS 
	@CONT_ID INT,
	@CONTID INT OUTPUT
AS

SET NOCOUNT OFF
BEGIN TRANSACTION 

	SELECT @CONTID=CONT_ID FROM CONTATOS_TIPO WHERE CONT_ID=@CONT_ID
	IF @@ROWCOUNT = 0
	BEGIN
		DELETE FROM CONTATOS WHERE CONT_ID=@CONT_ID
		SELECT @CONTID = 9
	END
	ELSE
	BEGIN
		SELECT @CONTID=5
	END
	IF @@ERROR <> 0 ROLLBACK ELSE	COMMIT
	SELECT @CONTID = @CONTID
	
GO

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-06-20 : 11:44:57

Read about FOREIGN KEY Constraints and Cascading Referential Integrity Constraints in BOL. It might save you some headache (or actually give you more.)

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-20 : 13:03:51

This is how you should write your SP in order to isolate the statements. There is no need to start a TRANSACTION if there is no matching value in other table.

CREATE PROCEDURE dbo.SP_EXCLUI_CONTATOS
(
	@CONT_ID INT,
	@CONTID INT
)
AS

IF EXISTS (SELECT * FROM CONTATOS_TIPO WHERE CONT_ID=@CONT_ID)
	SELECT @CONTID = 5
ELSE
	BEGIN
		BEGIN TRAN

		DELETE
		FROM	CONTATOS
		WHERE	CONT_ID = @CONT_ID

		IF @@ERROR = 0
			COMMIT TRAN
		ELSE
			ROLLBACK TRAN
	
		SELECT	@CONTID = 9-- Return same value if delete is ok or delete fail?
	END

Also I think there is some logic missing. What if delete fails? You still return with a value of 9.

Peter Larsson
Helsingborg, Sweden

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-20 : 13:14:26

There is no point using a transaction here since you aren't in a batch. You have one delete statement, so the transaction is already implied.

Tara Kizer
aka tduggan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-20 : 13:35:16

quote:
Originally posted by tkizer

There is no point using a transaction here since you aren't in a batch. You have one delete statement, so the transaction is already implied.

Tara Kizer
aka tduggan

True.

If the SP shown in orignal posting is complete SP or just an extract "to win some time"...

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources