SQL Stored Procedure to export specified data 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).
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

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;