Prev: output to html
Next: Read Uncommitted Data
From: DavidC on 22 Feb 2010 13:38
I have a stored procedure with several TSQL statements inside a transaction
as follows:

BEGIN TRAN T1;

....4 TSQL commands (inserts, etc.)

IF @ProgramID = 4
BEGIN
DECLARE @Notes nvarchar(100)
DECLARE @OrigDate date
SET @Notes = N'Check writing for check number ' + (SELECT CAST(CheckNumber
As varchar(8)) FROM VendorChecks WHERE CheckID = @CheckID);
SET @OrigDate = (SELECT CheckDate FROM VendorChecks WHERE CheckID =
@CheckID);

DELETE FROM dbo.ClientCaseNotes
WHERE ActivityID IN(SELECT ActivityID
FROM vw_ClientCaseNotesActivity
WHERE ActivityNotes = @Notes
AND CONVERT(CHAR(10),ActivityDate,101) =
CONVERT(CHAR(10),@OrigDate,101));
END

COMMIT TRAN T1;

The last DELETE inside the IF statement failed but the rest of the
inserts/updates got committed. I don't want a COMMIT unless ALL statements
succeed. How can I do this? Thanks.

--
David
From: Tony Rogerson on 22 Feb 2010 14:15
If you want that behavior then you must use error trapping....

begin tran

begin try


end try
begin catch

rollback


end catch

if @@tran_count > 0
commit tran


"DavidC" <dlchase(a)lifetimeinc.com> wrote in message
news:108D84EB-0C96-4159-900B-72CCFF0F03C9(a)microsoft.com...
> I have a stored procedure with several TSQL statements inside a
> transaction
> as follows:
>
> BEGIN TRAN T1;
>
> ...4 TSQL commands (inserts, etc.)
>
> IF @ProgramID = 4
> BEGIN
> DECLARE @Notes nvarchar(100)
> DECLARE @OrigDate date
> SET @Notes = N'Check writing for check number ' + (SELECT CAST(CheckNumber
> As varchar(8)) FROM VendorChecks WHERE CheckID = @CheckID);
> SET @OrigDate = (SELECT CheckDate FROM VendorChecks WHERE CheckID =
> @CheckID);
>
> DELETE FROM dbo.ClientCaseNotes
> WHERE ActivityID IN(SELECT ActivityID
> FROM vw_ClientCaseNotesActivity
> WHERE ActivityNotes = @Notes
> AND CONVERT(CHAR(10),ActivityDate,101) =
> CONVERT(CHAR(10),@OrigDate,101));
> END
>
> COMMIT TRAN T1;
>
> The last DELETE inside the IF statement failed but the rest of the
> inserts/updates got committed. I don't want a COMMIT unless ALL
> statements
> succeed. How can I do this? Thanks.
>
> --
> David

 | 
Pages: 1
Prev: output to html
Next: Read Uncommitted Data