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