SQL Sampler#

Just an example of an MSSQL procedure. Am looking at how the variable and table names are formed.

DROP PROCEDURE [extract].[populateCustomer]
GO

CREATE PROCEDURE [extract].[populateCustomer]
/********************************************************************************************************************
Name:          [extract].[populateCustomer]
Description:   Populates [extract].[Customer]
Parameters:    
Author:        Lemon Tree
Date:          25/2/2020
Modified:   
Testing:       
        exec [extract].[populateCustomer]
        select * from [extract].[Customer]
        select top 100 * from transform.LoadLog order by LoadLogID Desc

*********************************************************************************************************************/
AS
    -- Stats/error handling.
    DECLARE
        @Status NVARCHAR(20)
       ,@ProcType NVARCHAR(50)
       ,@ProcName NVARCHAR(50)
       ,@Table NVARCHAR(50)
       ,@StartTimeProc DATETIME
       ,@FinishTime DATETIME
       ,@Rows INT
       ,@ErrMsg NVARCHAR(4000)
       ,@ErrSeverity INT

    BEGIN TRY
        SELECT
            @StartTimeProc = GETDATE()
            ,@ProcType = 'Extract'
            ,@ProcName = '[extract].[populateCustomer]'
            ,@Table = '[extract].[Customer]';



        TRUNCATE TABLE [extract].[Customer]

        ; WITH CustomersPaidPayment AS
        (
            SELECT
                   BR1.[CustomerAddress]
                  ,BR1.[CustomerEmailAddress]
                  ,BR1.[CustomerName]
                  ,BR1.[CustomerSurname]
                  ,BR1.[CustomerMobileNumber]
                  ,BR1.[CustomerUsername]
                  ,BR1.[Marketing]
                  ,BR1.[BookingDate]
                  ,BR1.[BookingReference]
                  ,BR1.[StageRecordId]
             FROM source.BookingReportFiltered  BR1
        )
        ,MaxCustomer AS
        (
            SELECT CustomerUsername, MAX(StageRecordId) AS MaxStageRecordId
            FROM source.BookingReportFiltered 
            GROUP BY CustomerUsername
        )
        ,PriorBookings AS
        (
            SELECT BR4.CustomerUserName,count(*) as cnt
            FROM  source.BookingReportFiltered  BR4
            WHERE BR4.BookingDate < dateadd(day,-30,getdate())
            GROUP BY BR4.CustomerUserName
        )
        INSERT INTO [extract].[Customer]
        (
            [CustomerAddress]
            ,[CustomerEmailAddress]
            ,[CustomerName]
            ,[CustomerSurname]
            ,[CustomerMobileNumber]
            ,[CustomerUsername]
            ,[Marketing]
            ,NewCustomerFlag
            ,DateAdded
        )
        SELECT 
            BR1.[CustomerAddress]
            ,BR1.[CustomerEmailAddress]
            ,BR1.[CustomerName]
            ,BR1.[CustomerSurname]
            ,BR1.[CustomerMobileNumber]
            ,BR1.[CustomerUsername]
            ,BR1.[Marketing]
            ,CASE WHEN pb.Cnt is null THEN 1 ELSE 0 END AS NewCustomerFlag
            ,GETDATE() AS DateAdded
        FROM CustomersPaidPayment BR1
            JOIN MaxCustomer BR2
                ON BR2.MaxStageRecordId = BR1.StageRecordId
            LEFT JOIN PriorBookings pb
                ON pb.CustomerUserName = BR1.CustomerUserName

        SELECT
            @Rows = @@ROWCOUNT
           ,@FinishTime = GETDATE()
           ,@Status = 'Success'

        INSERT INTO [transform].[LoadLog]
        (
            [Status]
            ,[ProcType]
            ,ProcName
            ,[Table]
            ,StartTime
            ,FinishTime
            ,PackageDurationSeconds
            ,[RowCount] 
        )
        SELECT
            @Status
            ,@ProcType
            ,@ProcName
            ,@Table
            ,@StartTimeProc
            ,@FinishTime
            ,DATEDIFF(ss, @StartTimeProc, @FinishTime)
            ,@Rows;

    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 
            ROLLBACK;

        SELECT
            @Rows = @@ROWCOUNT
           ,@FinishTime = GETDATE()
           ,@Status = 'Failure'
           ,@ErrMsg = '--> Number ' + CAST(ERROR_NUMBER() AS VARCHAR) + ', Message ' + ERROR_MESSAGE() + ', State ' + CAST(ERROR_STATE() AS VARCHAR) + ', Line ' + CAST(ERROR_LINE() AS VARCHAR)
           ,@ErrSeverity = ERROR_SEVERITY();

        RAISERROR(@ErrMsg, @ErrSeverity, 1);

        INSERT  INTO [transform].[LoadLog]
        (
            [Status]
            ,ProcType
            ,ProcName
            ,[Table]
            ,StartTime
            ,FinishTime
            ,PackageDurationSeconds
            ,[RowCount]
            ,[Message]
            ,ErrSeverity
        )
        SELECT
            @Status
            ,@ProcType
            ,@ProcName
            ,@Table
            ,@StartTimeProc
            ,@FinishTime
            ,DATEDIFF(ss, @StartTimeProc, @FinishTime)
            ,@Rows
            ,@ErrMsg
            ,@ErrSeverity;


    END CATCH;







GO