1. Create a stored procedure template with Try Catch
Copy the following code, and then create a new query, you can write the SQL statement, after execution, a your own stored procedures are established!
SETAnsi_nulls onGOSETQuoted_identifier onGO-- =============================================--AUTHOR:--DESCRIBE:-- =============================================CREATE PROCEDURE [dbo].[Up_insertjhbdata] --Stored Procedure name@CustomerName VARCHAR( -)--Parameters as BEGIN SETNOCOUNT on --high-performance, you must have DECLARE @Now DATETIME SET @Now = GETDATE()--All operations Guarantee uniform time BEGINTRY--write the SQL here ENDTRYBEGINCATCHDECLARE @ErrorMessage NVARCHAR(4000) ; DECLARE @ErrorSeverity INT ; DECLARE @ErrorState INT ; SELECT @ErrorMessage =error_message (),@ErrorSeverity =error_severity (),@ErrorState =error_state (); PRINT @ErrorMessage RAISERROR(@ErrorMessage,--Message text. @ErrorSeverity,--Severity. @ErrorState --State . ) ; RETURN -1 ; ENDCATCHEND
2. Create a stored procedure template with transactions
The control of the transaction is added to the template of the stored procedure with Try Catch
SETAnsi_nulls onGOSETQuoted_identifier onGO-- =============================================--AUTHOR:--DESCRIBE:-- =============================================CREATE PROCEDURE [dbo].[Up_insertjhbdata] --Stored Procedure name@CustomerName VARCHAR( -) --Parameters as BEGIN SETNOCOUNT on;--high-performance, you must have DECLARE @Now DATETIME ; SET @Now = GETDATE() ;--All operations Guarantee uniform time BEGINTRYBEGIN TRANSACTIONMytrans;--Start a transaction --write the SQL here COMMIT TRANSACTIONMytrans;--Transaction COMMIT Statement ENDTRYBEGINCATCHROLLBACK TRANSACTIONMytrans--always roll back a transaction --Throw Exception DECLARE @ErrorMessage NVARCHAR(4000) ; DECLARE @ErrorSeverity INT ; DECLARE @ErrorState INT ; SELECT @ErrorMessage =error_message (),@ErrorSeverity =error_severity (),@ErrorState =error_state (); RAISERROR(@ErrorMessage,--Message text. @ErrorSeverity,--Severity. @ErrorState --State . ) ; ENDCATCHEND
3. Cycle templates
In a stored procedure, some temporary tables are often generated, and then the data for the staging table is processed, and the following templates help the partners to quickly handle such requirements
--Generate temporary table data with line numbers and insert in temporary table #t_table SELECTRow_number () Over(ORDER byID) asRownum,name into#T_Table fromTableName--get Total Records DECLARE @RecordCount INT = 0 SELECT @RecordCount = COUNT(1) from#T_TableDECLARE @CurrRowNum INT = 1 --Current line number DECLARE @CurrName VARCHAR( -)--Current Field --Circular Recording while @CurrRowNum <= @RecordCount BEGIN --get current record SELECT @CurrName =Name from#T_TableWHERERowNum= @CurrRowNum --Custom SQL SET @CurrRowNum = @CurrRowNum + 1 --to the next record END
Application of SQL Server stored procedures