Using SQL Variables as Column Headers with dynamic SQL

Question

I have a pretty long stored procedure (in SQL Server) where a business user asked if it would be possible to rename columns with values that are being stored in declared variables.

Here are variables declared at top of stored procedure:

DECLARE @BeginDate  datetime = '12/31/23';
DECLARE @PlusBeginDate datetime = DATEADD(DAY, 1, @BeginDate);
DECLARE @EndDate datetime = DATEADD(MONTH, 120, EOMONTH(DATEADD(DAY, 1, @PlusBeginDate)));
DECLARE @IncludePaidPortion bit = 0;
DECLARE @MonthAnnual char = 'M';
DECLARE @FinanceCompany varchar(max) = '1';
DECLARE @IncludeOnStream bit = 0;
DECLARE @TranCode varchar(max) = '1,35';
DECLARE @ContractBookType varchar(max) = 'capital';
DECLARE @FinanceProduct varchar(max) = '1,2,3';
DECLARE @IncludedStatus varchar(max) = '0';
DECLARE @IncludeTerminated bit = 0;
DECLARE @FundingSource varchar(max) = '0';
DECLARE @FinanceProgram varchar(max) = '20734';
DECLARE @ADC varchar(max) = '0';
DECLARE @IncludeResidual bit = 0;

It is apart of a larger stored procedure but here is what I have in regards to dynamic SQL:

DECLARE @sqlQuery NVARCHAR(MAX);
DECLARE @CaseDate datetime = '12/31/9999';

SET @sqlQuery = '
    SELECT
        MAX(ContractId) as ContractId,
        MAX([Maturity Date]) as [Maturity Date],
        MAX([Extended Maturity Date]) as [Extended Maturity Date],
        @PlusBeginDate AS ReportBegin,
        @EndDate AS ReportEnd,
        SUM(PeriodCash1) as [' + QUOTENAME(@PeriodValue1) + '],
        SUM(PeriodCash2) as [' + QUOTENAME(@PeriodValue2) + ']
    FROM
                    (SELECT 
                        Cash.ContractOid
                        ,Cash.TransactionCodeOid
                        ,Cash.DueDate
                        ,ISNULL(Contract.ContractId,''N/A'') as ContractId
                        ,lc.AccountDistributionCodeOid
                        ,ContractTerm.MaturityDate as [Maturity Date]
                        ,CASE
                            WHEN ContractTerm.MaturityDate <> lc.[Maturity Date] 
                                THEN lc.[Maturity Date] 
                            ELSE ''''
                        END as [Extended Maturity Date]
                        ,CASE 
                            WHEN DueDate BETWEEN @PeriodBegin1 AND @PeriodEnd1 
                            AND DueDate <= ISNULL(Contract.TerminationDate,@CaseDate)
                                THEN Amount
                            ELSE 0  
                        END AS PeriodCash1      
                        ,CASE 
                            WHEN DueDate BETWEEN @PeriodBegin2 AND @PeriodEnd2
                            AND DueDate <= ISNULL(Contract.TerminationDate,@CaseDate)
                                THEN Amount
                            ELSE 0  
                        END AS PeriodCash2      
                    FROM #Cash Cash
                    INNER JOIN #LimitedContracts lc on lc.ContractOid = Cash.ContractOid
                    INNER JOIN dbo.Contract ON Cash.ContractOid = Contract.ContractOid
                    LEFT JOIN dbo.ContractTerm ON ContractTerm.ContractOid = Contract.ContractOid AND ContractTerm.IsPrimary = 1
                    inner join dbo.Product p on p.oid = ContractTerm.ProductOid
                    LEFT JOIN dbo.Entity FinComp ON FinComp.oid = Contract.CompanyOid   
                    LEFT JOIN dbo.Entity On Entity.oid = Contract.EntityOid  
                    LEFT JOIN dbo.Status ON Status.oid = Contract.StatusOid
                    LEFT JOIN dbo.TransactionCode ON TransactionCode.TransactionCodeOid = Cash.TransactionCodeOId
                    LEFT JOIN dbo.AccountDistributionCode ADC ON ADC.AccountDistributionCodeOid = lc.AccountDistributionCodeOid
                    ) SubQuery
            GROUP BY SubQuery.ContractOid, SubQuery.TransactionCodeOid
            ORDER BY ContractID';

EXEC sp_executesql @sqlQuery;

For some reason it is returning the following errors:

Msg 137, Level 15, State 2, Line 6
Must declare the scalar variable "@PlusBeginDate".

Msg 103, Level 15, State 4, Line 8
The identifier that starts with '[January 2024], SUM(PeriodCash2) as [[February 2024] FROM (SELECT C' is too long. Maximum length is 128.

As you can see PlusBeginDate is declared, and this error does not occur if I only run everything prior to the dynamic SQL.

The length error doesn't make much sense either, as each variable is as follows: "MONTH YYYY" never comes close to 128 characters.

Any ideas on what I am doing wrong?

Answer 1

So you have two separate, and unrelated errors here, so I'll discuss each in turn.

The first error:

Msg 137, Level 15, State 2, Line 6 Must declare the scalar variable "@PlusBeginDate".

This is because you are using a variable declared outside the scope of the dynamic SQL, but it's not being provided to the dynamic SQL. When you call sp_executesql you have to pass in those parameters explicitly, or else it's just like they were never declared.

So for your example, your call to sp_executesql should look something like

exec sp_exceutesql
    @sqlQuery,
    -- Variables you use within your dynamic sql
    N'@PeriodBegin1 date,
        @PeriodBegin2 date,
        @PlusBeginDate...
        ... <all other variables you are using within your dynamic sql',
    -- Variables outside your dynamic sql you want passed into your dynamic sql
    @PeriodBegin1,
    @PeriodBegin2,
    @PlusBeginDate...
    ... 
    /*
    <all the variables you want to pass into the procedure. use the same order as how you
    defined them in your parameters block>
    */

That will get the first error to go away by making your dynamic sql have those values in scope.

The second error:

Msg 103, Level 15, State 4, Line 8 The identifier that starts with '[January 2024], SUM(PeriodCash2) as [[February 2024] FROM (SELECT C' is too long. Maximum length is 128.

This is happening because you aren't quoting your @PeriodValue correctly. The correct syntax is ' + quotename(@PeriodValue) + ' not [' + quotename(@PeriodValue) + ']. The way quotename works for square brackets is by escaping (i.e. doubling) all left square brackets within your string, the wrapping the resulting statement in square brackets. However since you are manually adding another level of square brackets around the outside, you're re-opening your column name brackets, and never closing them. Hence, it's treating all the subsequent SQL after your quotename section as part of the column alias. And since a column name can't exceed 128 characters, it's throwing that error.

TL;DR, remove those extra square brackets around that quotename and I think that should be fixed as well.