CREATE proc [dbo].[sys_rebate_equity] asDeclare @fMemberID varchar( -)--User IDDeclare @Rebate decimal( -,2)--total number of shares BEGIN beginTryBegin Transaction --Start a transaction DECLARECursor1CURSOR for --Defining Cursors Cursor1 SelectFmemberid,sum(Fnumber) as 'Fnumber' fromBp_equitywhereFnumber>0 Group byFmemberid--objects that use cursors OpenCursor1--Open Cursor Fetch Next fromCursor1 into @fMemberID,@Rebate --move the cursor down by 1 rows to get the data into the previously defined variable @fmemberid, @Rebate while @ @FETCH_STATUS=0 --determine if the data was successfully obtained begin Select 1 --to handle accordingly Fetch Next fromCursor1 into @id --move the cursor down by 1 rows End CloseCursor1--Close Cursors deallocateCursor1--To delete a cursor reference Select 888 Commit Transaction --Commit a transaction EndTryBeginCatchif @ @trancount > 0 ROLLBACK TRANSACTION --rolling back a transaction Select 8888 EndCatchEND
SQL stored procedures, using transactions (Try Catch), cursors