How to use RollBack in Stored Procedure in SQL Server

Last Reply on Jan 07, 2014 02:41 AM By Mudassar

Posted on Jan 07, 2014 02:40 AM
USE [Test_DataBaseNew]
GO

ALTER PROCEDURE [dbo].[InsertMovies]
	@MovieID int,
	@MovieName nvarchar(50),
	@Director nvarchar(50),
	@dt As Movies_Actor readonly
--	@dt as MoviesActor readonly
AS
BEGIN
	insert into Movies (MovieName, Director, MovieID) values(@MovieName, @Director, @MovieID)
	
	insert MovieDetails (MovieID, ActorID )
	select MovieID, ActorID from @dt
	
END

 

Posted on Jan 07, 2014 02:41 AM

This way

CREATE PROCEDURE MySP
AS
BEGIN
	BEGIN TRANSACTION;

	BEGIN TRY
		-- Your Queries
		COMMIT TRANSACTION;
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION;
	END CATCH;
END
GO