July 6, 2011 at 4:10 am
Hi Friends,
I've three tables viz : Classes, Students, Logs.
Classes - Store records of one single class, no. of students present/absent etc.
Students - Store records of multiple students with ClassId of above table.
Logs - Store records of edited students field.
The Stored procedure itself is understable.
I want to add Transaction to the following procedure.
ie add SavePoint, Begin transaction, RollBack Transaction, Trap Error, Goto statement etc.
As I'm lil bit new to add transactions. So, I need some suggestions regarding from where to start
transaction, rollback and commit transaction.
CREATE PROCEDURE ClassLogs
(
.
.
.
)
Select @ClassCount = "Select Count(*) From Classes Table....."
IF (@ClassCount = 0)
EXEC @sQry = Classes_Insert "Insert into Classes Table..."
EXEC @sQry = Students_Insert "Insert 1 Record into Students Table..."
ELSE IF (@ClassCount > 0)
SELECT @ClassId= "Select ClassId From Classes Table..."
SELECT @StudentCount = "Select Count(*) From Students Table..."
IF (@StudentCount = 0)
EXEC @sQry = Students_Insert "Insert Remaining Records into Students Table..."
ELSE IF (@StudentCount > 0)
SELECT @StudentsId = "...."
SELECT @status = "...."
EXEC @sQry = Logs_Insert
EXEC @sQry = Students_Update
EXEC @sQry = Classes_Update
Thanks In Adv,
July 6, 2011 at 4:18 am
Hmmm... doesn't look like T-SQL code for SQL Server.
Are you sure?
-- Gianluca Sartori
July 6, 2011 at 4:19 am
What do you mean by "understable"?
Use TRY...CATCH to trap errors. I can't be any more specific unless you provide more details of what you're trying to achieve.
John
July 6, 2011 at 4:21 am
I usually work with this template:
CREATE PROCEDURE <ProcedureName, sysname, >
AS
BEGIN
SET NOCOUNT ON
DECLARE @localTran bit
IF @@TRANCOUNT = 0
BEGIN
SET @localTran = 1
BEGIN TRANSACTION LocalTran
END
BEGIN TRY
--Insert code here
IF @localTran = 1 AND @@TRANCOUNT > 0
COMMIT TRAN LocalTran
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000)
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE()
IF @localTran = 1 AND @@TRANCOUNT > 0
ROLLBACK TRAN
RAISERROR ( @ErrorMessage, @ErrorSeverity, @ErrorState)
END CATCH
END
Hope this helps
Gianluca
-- Gianluca Sartori
July 6, 2011 at 4:35 am
Thanks for your Effort....
The logic of stored procedure is simple. I request to go through the meaning of tables.
I'm inserting all values using for loop from front end.
First if Save one record in Classes Table and one Record in Students Table.
Here class Attendance is completed.
When the loop executes second time (Else If ...), it saves all students records in Students table with ClassId.
I want if there is any error while Inserting students then Record Inserted in any
table or class table should be rolled back. Otherwise Commit.
July 6, 2011 at 4:37 am
Then you can safely use my template.
-- Gianluca Sartori
July 6, 2011 at 4:39 am
Yes Its lil bit Closer...
Can you tell me how and from where to start second transaction ?
As there is another If - else inside Else If Statement.
Here I'm inserting records in Logs Table and updating Classes and Students table.
If the Updation in not done then RollBack transaction.
July 6, 2011 at 5:55 am
Do everything you need to do where you find "insert code here". You can handle everything with a single transaction and rollback the whole work if something goes wrong.
At least, this is what I would expect from a stored procedure.
-- Gianluca Sartori
July 6, 2011 at 5:57 am
ok Thanks
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply