1 -- =============================================2 --Author: < wah Tsai >3 --Create Date: <2016,6,7>4 --Description: < species statistics >5 --update_qt_fishtypecount 0,0,0,0--test can run properly6 --Maintenance log:7 --8 -- =============================================9 ALTER PROCEDURE [dbo].[Update_qt_fishtypecount]Ten @RoomLevel INT, One @FishType INT, A @AppearNumber INT, - @CatchNumber INT - the as - BEGIN - - SETNOCOUNT on; + - BEGINTRY--Error Trapping + BEGIN TRAN;--Start a transaction A at withN as( - SELECT @RoomLevel asRoomlevel -,CONVERT(CHAR(Ten),GETDATE(), at) ascountdate -,@FishType asFishtype -,@AppearNumber asAppearnumber -,@CatchNumber asCatchnumber in ) -MERGE Qt_fishtypecount asFTC toUSING N onN.countdate=Ftc. Countdate andN.roomlevel=Ftc. Roomlevel andN.fishtype=FTC. Fishtype + when notmatched - Then INSERT the ( * [Roomlevel], $ [countdate],Panax Notoginseng [Fishtype], - [Appearnumber], the [Catchnumber] + ) A VALUES the ( +N.[Roomlevel], -N.[countdate], $N.[Fishtype], $N.[Appearnumber], -N.[Catchnumber] - ) the whenmatched - Then UPDATE SET [Appearnumber] =Ftc.[Appearnumber] + @AppearNumber,[Catchnumber] =Ftc.[Catchnumber] + @CatchNumber;Wuyi the COMMIT TRAN --Execute ERROR-free, COMMIT transaction - ENDTRY--Error Trapping Wu BEGINCatch--Error Trapping - ROLLBACK TRAN --performing an error, rolling back a transaction About INSERT into [Qpaccountsdb].[dbo].[Qa_errorlog] $([errornumber] -,[errorseverity] -,[errorstate] -,[errorprocedure] A,[ErrorLine] +,[errormessage] the,[Errortime]) - SELECTerror_number () $ , error_severity () the , Error_state () the,'QPTreasureDB.dbo.Update_QT_FishTypeCount' the , Error_line () the , Error_message () -,GETDATE() in ENDCatch--Error Trapping the END
View Code
Use merge to generate or update new records