Stored Procedure Help - Validate Before Deleting

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

General SQL Server Forums

New to SQL Server Programming

Stored Procedure Help - Validate Before Deleting

Author

Topic

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2011-02-23 : 18:01:40

ALTER PROCEDURE [dbo].[RequestInformatio]
(
@reqno numeric(15)
)
AS
SET NOCOUNT ON
BEGIN
begin tran

DELETE
FROM RequestInformation
WHERE requestno = @reqno

DELETE
FROM request_check_list_item
WHERE requestno = @reqno
commit tran
END

In the above Stored Procedure I wanted to add a validation such that it should delete only if approveddate column for a request in Reportnumber Table is null otherwise it should not delete

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-02-23 : 21:32:11

Your transaction is improperly formed. Are you using 2005 or 2008? If so, you need to read up on TRY/CATCH.

If you are going to use the transaction logic you currently have, then you have to add checks of @@ERROR after EVERY DML.

IF EXISTS (SELECT * FROM Reportnumber WHERE requestno = @reqno AND approveddate IS NULL)
...

[EDIT] Edited out a double-negative, lol.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-23 : 22:02:12

how is Reportnumber table link to the RequestInformation and request_check_list_item table ?

KH
[spoiler]Time is always against us[/spoiler]

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-24 : 00:23:27

quote:
Originally posted by tkizer

If you are going to use the transaction logic you currently have, then you have to add checks of @@ERROR after EVERY DML.

The highlighted: is it necessary? whats the logic behind this? I would appreciate if you could explain it with an example. Many thanks!

as far i know if you go with explicit transactions then if any of the unit level transaction fails it will Rollback everything to the starting point.

Cheers
MIK

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2011-02-24 : 08:05:55

All the three tables are joined using the reqno column

quote:
Originally posted by khtan

how is Reportnumber table link to the RequestInformation and request_check_list_item table ?

KH
[spoiler]Time is always against us[/spoiler]

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2011-02-24 : 08:07:46

We are using SQL 2005

quote:
Originally posted by tkizer

Your transaction is improperly formed. Are you using 2005 or 2008? If so, you need to read up on TRY/CATCH.

If you are going to use the transaction logic you currently have, then you have to add checks of @@ERROR after EVERY DML.

IF EXISTS (SELECT * FROM Reportnumber WHERE requestno = @reqno AND approveddate IS NULL)
...

[EDIT] Edited out a double-negative, lol.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-02-24 : 12:25:23

quote:
Originally posted by MIK_2008

quote:
Originally posted by tkizer

If you are going to use the transaction logic you currently have, then you have to add checks of @@ERROR after EVERY DML.

The highlighted: is it necessary? whats the logic behind this? I would appreciate if you could explain it with an example. Many thanks!

as far i know if you go with explicit transactions then if any of the unit level transaction fails it will Rollback everything to the starting point.

I don't have time to explain it at the moment, so please do some googling on this.

It is much better and easier to use TRY/CATCH along with your transaction to avoid having to check @@ERROR after every DML.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-24 : 12:29:26

many thanks for your response ..

Cheers
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources