Application of SQL Server stored procedures

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