SQL Stored Procedure to export specified data

Question

I'm currently using a stored procedure(sproc) to export to Excel. The sproc is being passed two parameters for @month and @year. The parameters are then passed to a dynamic pivot table in order to display all the days of the month for the specified month and year.

It does currently work, however are there better ways I could improve my code in order for it be more efficient? Any help or advice would be greatly appreciated as I'm still a bit of a beginner with SQL (and C# for that matter).

Alter Procedure stpDateRange
-- Add parameters
@month int , @year int
As
Begin
Set NoCount ON
 DECLARE @Dates NVARCHAR(max);
SELECT @Dates = CONCAT(@Dates + ', ', QUOTENAME(BalDate)) 
FROM tblID
WHERE YEAR(BalDate) = @year
 AND MONTH(BalDate) = @month 
GROUP BY BalDate
ORDER BY BalDate;
DECLARE @DynSql NVARCHAR(max);
SET @DynSql = 'SELECT *
FROM
(
 SELECT a1.IDNbr , a2.[CustName] as [Name] , (CAST(a1.BalDate AS date)) as BalDate, a1.Balance
 FROM tblID a1
 RIGHT JOIN tblCust a2 ON (a1.IDNbr = a2.IDNbr)
 WHERE MONTH(a1.BalDate) = @month
 AND YEAR(a1.BalDate) = @year AND a2.CustType != ''Inactive'') as d1
PIVOT (
 Sum(Balance)
 FOR BalDate IN ('+ @Dates +')
) piv';
DECLARE @Params NVARCHAR(500) = N'@year int, @month int'; 
-- SELECT @DynSql AS DynSql;
 EXECUTE sp_executesql @DynSql, @Params, @year = @year, @month=@month;
End

Test results (dates will continue until the last day of the month):

+-----------------------------------------------------------------+
| IDNbr | Name | 1/1/2018 | 1/2/2018 | 1/3/2018 | 1/4/2018 | ..|
|-----------------------------------------------------------------|
| 52852 | CustOne | 52028.52 | 52038.59 | 52048.69 | 52058.89 | ..|
| 39512 | CustTwo | 95125.75 | 95225.75 | 95325.75 | 95425.75 | ..|
| 52852 | CustThr | 86225.95 | 87225.95 | 88225.95 | 89225.95 | ..|
| 52852 | CustFor | 12533.12 | 12543.12 | 12553.12 | 12563.12 | ..|
| 52852 | CustFiv | 69585.36 | 69685.36 | 69785.36 | 69885.36 | ..|
| ..... | ....... | ........ | ........ | ........ | ........ | ..|

Readability for the results isn't awful , so that wouldn't be a concern for me, especially with how small the program is that is running this. My main concern is the performance of the stored procedure (sproc).

• aduguid: Can you post some example records?
• Jaskier: @aduguid Added test results!

Answer 1

You could always have 31 days by day number as column headers in the pivot. That way it would always be a consistent header and you wouldn't need to use dynamic SQL. Side note: Go K-State!

---

Results

screenshot

Example SQL

DECLARE @month AS INT = 1;
DECLARE @year AS INT = 2018;
WITH
tblCust
AS
(
 SELECT tblCust.* FROM (VALUES
 ( 52852, 'CustOne', 'Active')
 , ( 39512, 'CustTwo', 'Active')
 , ( 52852, 'CustThr', 'Active')
 , ( 52853, 'CustFor', 'Active')
 , ( 52854, 'CustFiv', 'Active')
 ) tblCust ([IDNbr], [CustName], [CustType]) 
)
, 
tblID
AS
(
 SELECT tblID.* FROM (VALUES
 ( 52852, '01-Jan-2018', 52028.52)
 , ( 52852, '02-Jan-2018', 52038.59)
 , ( 52852, '03-Jan-2018', 52048.69)
 , ( 52852, '04-Jan-2018', 52058.89)
 , ( 39512, '01-Jan-2018', 95125.75)
 , ( 39512, '02-Jan-2018', 95225.75)
 , ( 39512, '03-Jan-2018', 95325.75)
 , ( 39512, '04-Jan-2018', 95425.75)
 , ( 52852, '01-Jan-2018', 86225.95)
 , ( 52852, '02-Jan-2018', 87225.95)
 , ( 52852, '03-Jan-2018', 88225.95)
 , ( 52852, '04-Jan-2018', 89225.95)
 , ( 52853, '01-Jan-2018', 12533.12)
 , ( 52853, '02-Jan-2018', 12543.12)
 , ( 52853, '03-Jan-2018', 12553.12)
 , ( 52853, '04-Jan-2018', 12563.12)
 , ( 52854, '01-Jan-2018', 69585.36)
 , ( 52854, '02-Jan-2018', 69685.36)
 , ( 52854, '03-Jan-2018', 69785.36)
 , ( 52854, '04-Jan-2018', 69885.36)
 ) tblID ([IDNbr], [BalDate], [Balance]) 
)
SELECT *
FROM
(
 SELECT 
 a1.[IDNbr] 
 , [Name] = a2.[CustName]
 , [BalDateMonth] = DATEFROMPARTS(@year, @month, 1)
 , [DayOfMonth] = DATEPART(d, a1.[BalDate])
 , a1.[Balance]
 FROM 
 tblID AS a1 
 RIGHT JOIN tblCust AS a2 ON (a1.[IDNbr] = a2.[IDNbr])
 WHERE 
 1=1
 AND MONTH(a1.[BalDate]) = @month
 AND YEAR(a1.[BalDate]) = @year 
 AND a2.[CustType] != 'Inactive') as d1
PIVOT 
(
 Sum([Balance])
 FOR [DayOfMonth] IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31])
) piv;

• Jaskier: I currently have it set to dynamic SQL due to the winForm that's ending parameters allows the user to select a month. Instead of creating a stored procedure for every month, I'm having it pull all the data dynamically, so it fills it on its own