SQL Server stored Procedure usage tips

1. Create with Try ... Stored procedure templates for 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!

 Use [DB]--set the corresponding databaseGOSETAnsi_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

View Code

2. Create a stored procedure template with transactions

　　Just take a Try ... The control of the transaction is added to the template of the catch stored procedure, using a similar

 Use [DB]GOSETAnsi_nulls onGOSETQuoted_identifier onGO-- =============================================--AUTHOR:--DESCRIBE:-- =============================================CREATE PROCEDURE [dbo].[Up_insertjhbdata]--Stored Procedure name--Parameters    (      @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

View Code

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

View Code

SQL Server stored Procedure usage tips