|
Transaction is a logical unit of work that should be entirely completed or abort, no intermediate state is allowed. For example, if you sell a product to a customer, your transaction consists of at least two parts: you must update the inventory by subtracting the number of unites sold , and you must update the accounts receivable table in order to bill the customer. You want either updating both tables or neither. Updating only one is not acceptable.
In SQL you use "Begin Transaction" to mark the beginning of a transaction. Normally, if there is error--fail one activity(for example updating), you don't want to execute the others in the same transaction, you use "ROLLBACK TRANSACTION". Check error after each and every activity, if fail then rollback. use "COMMIT TRANSACTION" to commit changes only when all activities succeed.
@@ERROR <>0 only checks the latest sql statement. So better use it after each statement.
if you want to execute more than one statement after "IF" or "ELSE", put them between "BEGIN" and "END".
Use "EXEC procudureName" you can execute other procedures within procedures.
Here is an example:
SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO
CREATE PROCEDURE [dbo].[StoreprocedureName1] ( @Col1 varchar(255), @Col2 varchar(100), @EmailID CHAR(1)='0' ) AS
SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRANSACTION
INSERT TableName (ColumnName1,ColumnName2) VALUES (@Col1,@Col2)
IF @@ERROR <>0 BEGIN ROLLBACK TRANSACTION RAISERROR('Insertion failed.',16,1) RETURN -1 END ELSE BEGIN IF @EmailID = '1' BEGIN EXEC StoreprocedureName
IF @@ERROR <>0 BEGIN ROLLBACK TRANSACTION RAISERROR('StoreprocedureName execution failed.',16,1) RETURN -1 END END END COMMIT TRANSACTION
GO
SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
|
|
|