Link to home
Start Free TrialLog in
Avatar of matrix_aash
matrix_aashFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Transaction

I have the following sp which i want to put in a transaction so that if errors it rollback to its original statment:

Can somebody please help me in putting the begin transaction and the if @@ROWCOUNT <> 0 commit transaction and if @@ROWCOUNT = 0 rollback transaction


BEGIN
BEGIN TRY
SELECT  DISTINCT(a.PacketID)
FROM
(
SELECT (CANDIDATESCRIPT.PACKETID),COUNT(CANDIDATESCRIPTIMAGE.IMAGEFSADDR) AS ACTUALPAGES,
QUESTIONPAPER.EXPECTEDPAGECOUNT AS EXPECTEDPAGECOUNT
FROM CANDIDATESCRIPTIMAGE,CANDIDATESCRIPT,QUESTIONPAPER,SCANNEDPACKET
WHERE CANDIDATESCRIPT.CANDIDATESCRIPTKEY = CANDIDATESCRIPTIMAGE.CANDIDATESCRIPTID
AND QUESTIONPAPER.BARCODE = CANDIDATESCRIPT.QUESTIONPAPERBARCODE AND (CANDIDATESCRIPT.STATUS IS NULL OR 
CANDIDATESCRIPT.STATUS = 5) AND CANDIDATESCRIPT.PACKETID = SCANNEDPACKET.SCANNEDPACKETID 
AND SCANNEDPACKET.SCANNEDPACKETSTATUS = 'Arrived' 
GROUP BY CANDIDATESCRIPTIMAGE.CANDIDATESCRIPTID,CANDIDATESCRIPT.SCANSCRIPTID,
CANDIDATESCRIPT.STATUS,CANDIDATESCRIPT.QUESTIONPAPERBARCODE,
QUESTIONPAPER.EXPECTEDPAGECOUNT,CANDIDATESCRIPT.PACKETID,
CANDIDATESCRIPT.SCANDATE
HAVING COUNT(CANDIDATESCRIPTIMAGE.IMAGEFSADDR) <> QUESTIONPAPER.EXPECTEDPAGECOUNT
) a
IF @@ROWCOUNT <> 0
UPDATE sp
SET SCANNEDPACKETSTATUS = 'QC1'
FROM
SCANNEDPACKET sp
JOIN
(
SELECT  DISTINCT(a.PacketID)
FROM
(
SELECT (CANDIDATESCRIPT.PACKETID),COUNT(CANDIDATESCRIPTIMAGE.IMAGEFSADDR) AS ACTUALPAGES,
QUESTIONPAPER.EXPECTEDPAGECOUNT AS EXPECTEDPAGECOUNT
FROM CANDIDATESCRIPTIMAGE,CANDIDATESCRIPT,QUESTIONPAPER,SCANNEDPACKET
WHERE CANDIDATESCRIPT.CANDIDATESCRIPTKEY = CANDIDATESCRIPTIMAGE.CANDIDATESCRIPTID
AND QUESTIONPAPER.BARCODE = CANDIDATESCRIPT.QUESTIONPAPERBARCODE AND (CANDIDATESCRIPT.STATUS IS NULL OR 
CANDIDATESCRIPT.STATUS = 5) AND CANDIDATESCRIPT.PACKETID = SCANNEDPACKET.SCANNEDPACKETID 
AND SCANNEDPACKET.SCANNEDPACKETSTATUS = 'Arrived' 
GROUP BY CANDIDATESCRIPTIMAGE.CANDIDATESCRIPTID,CANDIDATESCRIPT.SCANSCRIPTID,
CANDIDATESCRIPT.STATUS,CANDIDATESCRIPT.QUESTIONPAPERBARCODE,
QUESTIONPAPER.EXPECTEDPAGECOUNT,CANDIDATESCRIPT.PACKETID,
CANDIDATESCRIPT.SCANDATE
HAVING COUNT(CANDIDATESCRIPTIMAGE.IMAGEFSADDR) <> QUESTIONPAPER.EXPECTEDPAGECOUNT
) a
) b ON sp.SCANNEDPACKETID = b.PACKETID
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() as ErrorState,
        ERROR_PROCEDURE() as ErrorProcedure,
        ERROR_LINE() as ErrorLine,
        ERROR_MESSAGE() as ErrorMessage;
 
 
END CATCH;
END

Open in new window

Avatar of chapmandew
chapmandew
Flag of United States of America image

Here you go....you had most of it already.
BEGIN
BEGIN TRANSACTION
 
BEGIN TRY
SELECT  DISTINCT(a.PacketID)
FROM
(
SELECT (CANDIDATESCRIPT.PACKETID),COUNT(CANDIDATESCRIPTIMAGE.IMAGEFSADDR) AS ACTUALPAGES,
QUESTIONPAPER.EXPECTEDPAGECOUNT AS EXPECTEDPAGECOUNT
FROM CANDIDATESCRIPTIMAGE,CANDIDATESCRIPT,QUESTIONPAPER,SCANNEDPACKET
WHERE CANDIDATESCRIPT.CANDIDATESCRIPTKEY = CANDIDATESCRIPTIMAGE.CANDIDATESCRIPTID
AND QUESTIONPAPER.BARCODE = CANDIDATESCRIPT.QUESTIONPAPERBARCODE AND (CANDIDATESCRIPT.STATUS IS NULL OR 
CANDIDATESCRIPT.STATUS = 5) AND CANDIDATESCRIPT.PACKETID = SCANNEDPACKET.SCANNEDPACKETID 
AND SCANNEDPACKET.SCANNEDPACKETSTATUS = 'Arrived' 
GROUP BY CANDIDATESCRIPTIMAGE.CANDIDATESCRIPTID,CANDIDATESCRIPT.SCANSCRIPTID,
CANDIDATESCRIPT.STATUS,CANDIDATESCRIPT.QUESTIONPAPERBARCODE,
QUESTIONPAPER.EXPECTEDPAGECOUNT,CANDIDATESCRIPT.PACKETID,
CANDIDATESCRIPT.SCANDATE
HAVING COUNT(CANDIDATESCRIPTIMAGE.IMAGEFSADDR) <> QUESTIONPAPER.EXPECTEDPAGECOUNT
) a
IF @@ROWCOUNT <> 0
UPDATE sp
SET SCANNEDPACKETSTATUS = 'QC1'
FROM
SCANNEDPACKET sp
JOIN
(
SELECT  DISTINCT(a.PacketID)
FROM
(
SELECT (CANDIDATESCRIPT.PACKETID),COUNT(CANDIDATESCRIPTIMAGE.IMAGEFSADDR) AS ACTUALPAGES,
QUESTIONPAPER.EXPECTEDPAGECOUNT AS EXPECTEDPAGECOUNT
FROM CANDIDATESCRIPTIMAGE,CANDIDATESCRIPT,QUESTIONPAPER,SCANNEDPACKET
WHERE CANDIDATESCRIPT.CANDIDATESCRIPTKEY = CANDIDATESCRIPTIMAGE.CANDIDATESCRIPTID
AND QUESTIONPAPER.BARCODE = CANDIDATESCRIPT.QUESTIONPAPERBARCODE AND (CANDIDATESCRIPT.STATUS IS NULL OR 
CANDIDATESCRIPT.STATUS = 5) AND CANDIDATESCRIPT.PACKETID = SCANNEDPACKET.SCANNEDPACKETID 
AND SCANNEDPACKET.SCANNEDPACKETSTATUS = 'Arrived' 
GROUP BY CANDIDATESCRIPTIMAGE.CANDIDATESCRIPTID,CANDIDATESCRIPT.SCANSCRIPTID,
CANDIDATESCRIPT.STATUS,CANDIDATESCRIPT.QUESTIONPAPERBARCODE,
QUESTIONPAPER.EXPECTEDPAGECOUNT,CANDIDATESCRIPT.PACKETID,
CANDIDATESCRIPT.SCANDATE
HAVING COUNT(CANDIDATESCRIPTIMAGE.IMAGEFSADDR) <> QUESTIONPAPER.EXPECTEDPAGECOUNT
) a
) b ON sp.SCANNEDPACKETID = b.PACKETID
END TRY
BEGIN CATCH
	ROLLBACK TRANSACTION
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() as ErrorState,
        ERROR_PROCEDURE() as ErrorProcedure,
        ERROR_LINE() as ErrorLine,
        ERROR_MESSAGE() as ErrorMessage;
 
 
END CATCH;
END
 
 
IF @@TRANCOUNT > 0
COMMIT TRANSACTION

Open in new window

BEGIN
begin tran
BEGIN TRY
SELECT  DISTINCT(a.PacketID)
FROM
(
SELECT (CANDIDATESCRIPT.PACKETID),COUNT(CANDIDATESCRIPTIMAGE.IMAGEFSADDR) AS ACTUALPAGES,
QUESTIONPAPER.EXPECTEDPAGECOUNT AS EXPECTEDPAGECOUNT
FROM CANDIDATESCRIPTIMAGE,CANDIDATESCRIPT,QUESTIONPAPER,SCANNEDPACKET
WHERE CANDIDATESCRIPT.CANDIDATESCRIPTKEY = CANDIDATESCRIPTIMAGE.CANDIDATESCRIPTID
AND QUESTIONPAPER.BARCODE = CANDIDATESCRIPT.QUESTIONPAPERBARCODE AND (CANDIDATESCRIPT.STATUS IS NULL OR
CANDIDATESCRIPT.STATUS = 5) AND CANDIDATESCRIPT.PACKETID = SCANNEDPACKET.SCANNEDPACKETID
AND SCANNEDPACKET.SCANNEDPACKETSTATUS = 'Arrived'
GROUP BY CANDIDATESCRIPTIMAGE.CANDIDATESCRIPTID,CANDIDATESCRIPT.SCANSCRIPTID,
CANDIDATESCRIPT.STATUS,CANDIDATESCRIPT.QUESTIONPAPERBARCODE,
QUESTIONPAPER.EXPECTEDPAGECOUNT,CANDIDATESCRIPT.PACKETID,
CANDIDATESCRIPT.SCANDATE
HAVING COUNT(CANDIDATESCRIPTIMAGE.IMAGEFSADDR) <> QUESTIONPAPER.EXPECTEDPAGECOUNT
) a
IF @@ROWCOUNT <> 0
UPDATE sp
SET SCANNEDPACKETSTATUS = 'QC1'
FROM
SCANNEDPACKET sp
JOIN
(
SELECT  DISTINCT(a.PacketID)
FROM
(
SELECT (CANDIDATESCRIPT.PACKETID),COUNT(CANDIDATESCRIPTIMAGE.IMAGEFSADDR) AS ACTUALPAGES,
QUESTIONPAPER.EXPECTEDPAGECOUNT AS EXPECTEDPAGECOUNT
FROM CANDIDATESCRIPTIMAGE,CANDIDATESCRIPT,QUESTIONPAPER,SCANNEDPACKET
WHERE CANDIDATESCRIPT.CANDIDATESCRIPTKEY = CANDIDATESCRIPTIMAGE.CANDIDATESCRIPTID
AND QUESTIONPAPER.BARCODE = CANDIDATESCRIPT.QUESTIONPAPERBARCODE AND (CANDIDATESCRIPT.STATUS IS NULL OR
CANDIDATESCRIPT.STATUS = 5) AND CANDIDATESCRIPT.PACKETID = SCANNEDPACKET.SCANNEDPACKETID
AND SCANNEDPACKET.SCANNEDPACKETSTATUS = 'Arrived'
GROUP BY CANDIDATESCRIPTIMAGE.CANDIDATESCRIPTID,CANDIDATESCRIPT.SCANSCRIPTID,
CANDIDATESCRIPT.STATUS,CANDIDATESCRIPT.QUESTIONPAPERBARCODE,
QUESTIONPAPER.EXPECTEDPAGECOUNT,CANDIDATESCRIPT.PACKETID,
CANDIDATESCRIPT.SCANDATE
HAVING COUNT(CANDIDATESCRIPTIMAGE.IMAGEFSADDR) <> QUESTIONPAPER.EXPECTEDPAGECOUNT
) a
) b ON sp.SCANNEDPACKETID = b.PACKETID

if @@RowCount <> 0
commit
else
rollback

END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() as ErrorState,
        ERROR_PROCEDURE() as ErrorProcedure,
        ERROR_LINE() as ErrorLine,
        ERROR_MESSAGE() as ErrorMessage;
 
rollback
END CATCH;
END
 

momi, you want the rollback before you do anything in the error handler, otherwise any type data you would insert (although it is not done here) into an error table, etc would be rolled back.
Avatar of matrix_aash

ASKER

In the above statement I have select statement which shows the results when I run the stored procedure. How can i run the sp so that it just shows the return_value i.e 0 if success and another other value if not successfull.

by the way thanks for the above statment it works perfect.

Cheers
something like this, me thinks....
create procedure mysp
AS
BEGIN
BEGIN TRANSACTION
 
BEGIN TRY
SELECT  DISTINCT(a.PacketID)
FROM
(
SELECT (CANDIDATESCRIPT.PACKETID),COUNT(CANDIDATESCRIPTIMAGE.IMAGEFSADDR) AS ACTUALPAGES,
QUESTIONPAPER.EXPECTEDPAGECOUNT AS EXPECTEDPAGECOUNT
FROM CANDIDATESCRIPTIMAGE,CANDIDATESCRIPT,QUESTIONPAPER,SCANNEDPACKET
WHERE CANDIDATESCRIPT.CANDIDATESCRIPTKEY = CANDIDATESCRIPTIMAGE.CANDIDATESCRIPTID
AND QUESTIONPAPER.BARCODE = CANDIDATESCRIPT.QUESTIONPAPERBARCODE AND (CANDIDATESCRIPT.STATUS IS NULL OR 
CANDIDATESCRIPT.STATUS = 5) AND CANDIDATESCRIPT.PACKETID = SCANNEDPACKET.SCANNEDPACKETID 
AND SCANNEDPACKET.SCANNEDPACKETSTATUS = 'Arrived' 
GROUP BY CANDIDATESCRIPTIMAGE.CANDIDATESCRIPTID,CANDIDATESCRIPT.SCANSCRIPTID,
CANDIDATESCRIPT.STATUS,CANDIDATESCRIPT.QUESTIONPAPERBARCODE,
QUESTIONPAPER.EXPECTEDPAGECOUNT,CANDIDATESCRIPT.PACKETID,
CANDIDATESCRIPT.SCANDATE
HAVING COUNT(CANDIDATESCRIPTIMAGE.IMAGEFSADDR) <> QUESTIONPAPER.EXPECTEDPAGECOUNT
) a
IF @@ROWCOUNT <> 0
UPDATE sp
SET SCANNEDPACKETSTATUS = 'QC1'
FROM
SCANNEDPACKET sp
JOIN
(
SELECT  DISTINCT(a.PacketID)
FROM
(
SELECT (CANDIDATESCRIPT.PACKETID),COUNT(CANDIDATESCRIPTIMAGE.IMAGEFSADDR) AS ACTUALPAGES,
QUESTIONPAPER.EXPECTEDPAGECOUNT AS EXPECTEDPAGECOUNT
FROM CANDIDATESCRIPTIMAGE,CANDIDATESCRIPT,QUESTIONPAPER,SCANNEDPACKET
WHERE CANDIDATESCRIPT.CANDIDATESCRIPTKEY = CANDIDATESCRIPTIMAGE.CANDIDATESCRIPTID
AND QUESTIONPAPER.BARCODE = CANDIDATESCRIPT.QUESTIONPAPERBARCODE AND (CANDIDATESCRIPT.STATUS IS NULL OR 
CANDIDATESCRIPT.STATUS = 5) AND CANDIDATESCRIPT.PACKETID = SCANNEDPACKET.SCANNEDPACKETID 
AND SCANNEDPACKET.SCANNEDPACKETSTATUS = 'Arrived' 
GROUP BY CANDIDATESCRIPTIMAGE.CANDIDATESCRIPTID,CANDIDATESCRIPT.SCANSCRIPTID,
CANDIDATESCRIPT.STATUS,CANDIDATESCRIPT.QUESTIONPAPERBARCODE,
QUESTIONPAPER.EXPECTEDPAGECOUNT,CANDIDATESCRIPT.PACKETID,
CANDIDATESCRIPT.SCANDATE
HAVING COUNT(CANDIDATESCRIPTIMAGE.IMAGEFSADDR) <> QUESTIONPAPER.EXPECTEDPAGECOUNT
) a
) b ON sp.SCANNEDPACKETID = b.PACKETID
END TRY
BEGIN CATCH
        ROLLBACK TRANSACTION
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() as ErrorState,
        ERROR_PROCEDURE() as ErrorProcedure,
        ERROR_LINE() as ErrorLine,
        ERROR_MESSAGE() as ErrorMessage;
 
        RETURN(1)
END CATCH;
END
 
 
IF @@TRANCOUNT > 0
COMMIT TRANSACTION
RETURN(0)

Open in new window

It still displays the select results. I dont want the sp to return the select results from following statment:

SELECT  DISTINCT(a.PacketID)
FROM
(
SELECT (CANDIDATESCRIPT.PACKETID),COUNT(CANDIDATESCRIPTIMAGE.IMAGEFSADDR) AS ACTUALPAGES,
QUESTIONPAPER.EXPECTEDPAGECOUNT AS EXPECTEDPAGECOUNT
FROM CANDIDATESCRIPTIMAGE,CANDIDATESCRIPT,QUESTIONPAPER,SCANNEDPACKET
WHERE CANDIDATESCRIPT.CANDIDATESCRIPTKEY = CANDIDATESCRIPTIMAGE.CANDIDATESCRIPTID
AND QUESTIONPAPER.BARCODE = CANDIDATESCRIPT.QUESTIONPAPERBARCODE AND (CANDIDATESCRIPT.STATUS IS NULL OR
CANDIDATESCRIPT.STATUS = 5) AND CANDIDATESCRIPT.PACKETID = SCANNEDPACKET.SCANNEDPACKETID
AND SCANNEDPACKET.SCANNEDPACKETSTATUS = 'Arrived'
GROUP BY CANDIDATESCRIPTIMAGE.CANDIDATESCRIPTID,CANDIDATESCRIPT.SCANSCRIPTID,
CANDIDATESCRIPT.STATUS,CANDIDATESCRIPT.QUESTIONPAPERBARCODE,
QUESTIONPAPER.EXPECTEDPAGECOUNT,CANDIDATESCRIPT.PACKETID,
CANDIDATESCRIPT.SCANDATE
HAVING COUNT(CANDIDATESCRIPTIMAGE.IMAGEFSADDR) <> QUESTIONPAPER.EXPECTEDPAGECOUNT
) a

by the way the code by chapmandew worked fine earlier.

thanks
So, do you just want to see if the query returns results?  What do you need to see?
I dont want to see the results. What it is doing at the moment is its showing the results from the select statment and then a 0 which means the sp has ran successfully. I just want it later on to appear which it will if the sp is successfull.

I know i can remove the select statement and just run the update statment.

but i want to leave the select statement in the sp.

hope it make sense.
ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
what even the

--IF @@ROWCOUNT <> 0
this above line you mean

Cheers
You don't need it either...since you have the same query in your select statement for your update clause.  You're good to go...

Tim