Refactor Stored Procedures

RefactorStoredProcs.sql

--This is how you want your queries to behave. Short and sweet, to prevent blocking
--See: http://dbareactions.com/post/112726103627/how-queries-behave-after-developers-follow-my

--Benefits: 
--1.Doesn't block other users.
--2.Easy to read and understand quickly
--3.Easy to maintain
--4.Easy to debug/test

--check first before creating our temporary tables
IF OBJECT_ID('tempdb..#CompanyInfo') IS NOT NULL
	DROP TABLE #CompanyInfo;

IF OBJECT_ID('tempdb..#SIMInfo') IS NOT NULL
	DROP TABLE #SIMInfo;

CREATE TABLE #CompanyInfo (
	Company          [nvarchar](50),                       
	AccountNumber    [nvarchar](50),                       
	SalesForceID     [nvarchar](50),                       
    Address          [nvarchar](50),                      
	Address2         [nvarchar](50),                      
	Address3         [nvarchar](50),                      
	Postal           [nvarchar](50),           
	AccountRepresentative [nvarchar](50),    
	LocationID		 [int],					 
	LastName         [nvarchar](50),                        
	FirstName        [nvarchar](50),            
);

--Insert data for all locations
INSERT INTO #CompanyInfo (
	Company
	,AccountNumber
	,SalesForceID
	,Address
	,Address2
	,Address3
	,Postal
	,AccountRepresentative
	,LocationID
	)
SELECT c.Company
	,c.AccountNumber
	,c.SalesForceID
	,l.Address
	,l.Address2
	,l.Address3
	,l.Postal
	,c.AccountRepresentative
	,l.LocationID
FROM tbl_company c
JOIN tbl_locations l ON c.EnterpriseID = l.EnterpriseID;

--get our agent's data
UPDATE c
SET LastName = a.FirstName
   ,FirstName = a.LastName
FROM #CompanyInfo c
LEFT JOIN tbl_account_reps accountRepsTable ON accountRepsTable.AccountRepresentative = c.AccountRepresentative
LEFT JOIN tbl_agents a ON a.agentId = accountRepsTable.agentID

--SELECT * FROM #CompanyInfo

CREATE TABLE #SIMInfo ( 
	 MSISDN						[nvarchar](50)
	,SimStatus					[nvarchar](50)
	,SIMID						[int]
	,IMSI						[nvarchar](50)
	,IR							[char](1) DEFAULT N'N'
	,PAYR						[char](1) DEFAULT N'N'
	,SIMSMSPlanClassTypeName	[varchar](100)
	,SIMDataPlanClassTypeName	[varchar](100)
	,DisplayName				[nvarchar](5)
	,LocationID					[int]
	,CostCenterID				[int]
	)

--Insert all SIM info where IMSI is not null or <> ''
INSERT INTO #SIMInfo (
	LocationID
	,MSISDN
	,SimStatus
	,SIMID
	,IMSI
	)
SELECT LocationID
	,MSISDN
	,SimStatus
	,SIMID
	,IMSI
FROM tbl_sims
WHERE IMSI IS NOT NULL
	AND IMSI <> '';

--update columns sourced from tables other than tbl_sims
UPDATE s
SET s.DisplayName = cc.DisplayName
FROM #SIMInfo s
LEFT JOIN tbl_cost_centers cc ON s.CostCenterID = cc.CostCenterID;

UPDATE s
SET s.SIMSMSPlanClassTypeName = vw_GetCurrentSIMPlanForDevices.SIMPlanClassTypeName	
FROM #SIMInfo s
LEFT JOIN vw_GetCurrentSIMPlanForDevices vw_GetCurrentSIMPlanForDevices ON s.SimID = vw_GetCurrentSIMPlanForDevices.SimID AND vw_GetCurrentSIMPlanForDevices.UsageType=1;


UPDATE s
SET s.SIMDataPlanClassTypeName = vw_GetCurrentSIMPlanForDevices.SIMPlanClassTypeName
FROM #SIMInfo s
LEFT JOIN vw_GetCurrentSIMPlanForDevices vw_GetCurrentSIMPlanForDevices ON s.SimID = vw_GetCurrentSIMPlanForDevices.SimID
	AND vw_GetCurrentSIMPlanForDevices.UsageType = 1

UPDATE s
SET s.IR = 'Y'
FROM #SIMInfo s
JOIN tbl_sim_features sf ON s.SIMID = sf.SIMID
JOIN tbl_lookup_type_sim_Features ltsf ON sf.SimFeatureTypeID = ltsf.SimFeatureTypeID
WHERE ltsf.simfeaturetypeid = 3
	AND sf.SimFeatureStatus = 'Active';

UPDATE s
SET s.IR = 'Y'
FROM #SIMInfo s
JOIN tbl_sim_features sf ON s.SIMID = sf.SIMID
JOIN tbl_lookup_type_sim_Features ltsf ON sf.SimFeatureTypeID = ltsf.SimFeatureTypeID
WHERE ltsf.simfeaturetypeid IN (7,9)
	AND sf.SimFeatureStatus = 'Active';

--SELECT * FROM #SIMInfo

DECLARE @BILL_CYCLE NVARCHAR(10);
SELECT @BILL_CYCLE = CONVERT(VARCHAR(10),DateAdd(month,DATEDIFF(month, 0, GETDATE()), 0),103  ) 

--Join our 2 temporary tables for our final result set
SELECT 
		c.Company                   AS ACCOUNT_NAME, 
		c.AccountNumber             AS BRN_NO,
		c.SalesForceID              AS ACCOUNT_NO,
		s.MSISDN                    AS MSISDN,
		s.SimStatus                 AS SimState,
		s.SIMID                     AS SimID,
		s.DisplayName				AS COSTCENTER,
		s.SIMDataPlanClassTypeName  AS PROD_NAME,	--CurrentDataPlane
		c.LastName                  AS AM_LNAME,	--account rep first name
		c.FirstName                 AS AM_FNAME,	--account rep last name
		c.Address                   AS ADDR1,
		c.Address2                  AS ADDR2,
		c.Address3                  AS ADDR3,
		c.Postal                    AS ADDR5, 
		s.IMSI                      AS IMSI,
		s.IR,										--roaming data feature for sim
		s.PAYR,										--PayPerUseVoiceRoaming feature for sim
		@BILL_CYCLE					AS BILL_CYCLE,	--Get first of month of when this report was run
		SIMSMSPlanClassTypeName     AS M2M_Plan		--M2MCurrentSMSPlan --Current SMS plan
	FROM #CompanyInfo c
	JOIN #SimInfo s on c.LocationID = s.LocationId
	ORDER BY c.Company, s.SIMID
	
--clean up
DROP TABLE #CompanyInfo;
DROP TABLE #SIMInfo;