Hi Ashish,

See changes in bold below. The idea is to commit transaction in case when everithing is ok and rollback otherwise. Notice that because @@ERROR is cleared and reset on each statement executed it was saved to a local variable @Retcode.

Examples


>Hi All,
>
>is any one can chek the following s.p that i am using the right positions for begin trans, commit trans and error. i have doubt if insertion is failed what will happen it will first try to commit or will give an error number.
>
>thanks,
>

>CREATE Procedure udsp_insert_ExamMasterList
>@ExamID 		char (2),
>@ExamName 		varchar (20),
>@Fees 			money,
>@ExpiryMonths 		smallint,
>@ExamDescription 	varchar(100),
>@KdeId       	    	kdeid,
>@KdeDate		datetime
>
>As
<b>DECLARE @Retcode int</b>
>
>     set nocount on
>
>	begin transaction
>
>     Insert tbl_ExamMasterList
>	   (ExamID,ExamName,Fees,ExpiryMonths,ExamDescription,
>            kdeid, kdedate)
>     Values
>	   (@ExamID,@ExamName,@Fees,@ExpiryMonths,@ExamDescription,
>            UPPER(@kdeid), getdate())
>
<b>@Retcode = @@error</b>
>
>	if <b>@Retcode</b> <> 0
>           rollback
>	else
            <b>commit transaction</b>
>
<b>return @Retcode</b>