Create a SQL Stored Procedure with Dynamic Column Names

Question

I have the following stored procedure:

ALTER PROCEDURE [dbo].[sp_RequestCategoryCount]
    @StDate1 DATE,
    @EnDate1 DATE,
    @StDate2 DATE,
    @EnDate2 DATE
AS
BEGIN
    SELECT DISTINCT RQ.request_category
    INTO #ReqCat
    FROM 
        (SELECT DISTINCT request_category 
         FROM [VerInt_OneViewServiceReq]
         UNION ALL
         SELECT DISTINCT request_category 
         FROM [VerInt_OneViewServiceReq2]) RQ

    SELECT
        '1' as Iteration,
        request_category,
        SUM(1) AS Record_Count
    INTO 
        #Iter1
    FROM
        [VerInt_OneViewServiceReq]
    WHERE
        request_created_dt BETWEEN @StDate1 AND @EnDate1
    GROUP BY 
        request_category

    -- UNION ALL

    SELECT 
        '2' as Iteration,
        request_category,
        SUM(1) as Record_Count
    INTO 
        #Iter2
    FROM
        [VerInt_OneViewServiceReq2]
    WHERE
        request_created_dt BETWEEN @StDate2 AND @EnDate2
    GROUP BY
        request_category

    -- ORDER BY Iteration, request_category ASC

    SELECT 
        RC.request_category, 
        IT1.Record_Count as RecordCountDateRange1,
        IT2.Record_Count as RecordCountDateRange2
    FROM
        #ReqCat RC
    LEFT JOIN 
        #Iter1 IT1 ON IT1.request_category = RC.request_category
    LEFT JOIN 
        #Iter2 IT2 ON IT2.request_category = RC.request_category

    DROP TABLE #ReqCat
    DROP TABLE #Iter1
    DROP TABLE #Iter2
END

All it does is basically compare 2 timespans of data in 2 tables by lining them up side-by-side, and works fine.

What I'd like to do is replace this from the last SELECT statement:

SELECT
    RC.request_category, 
    IT1.Record_Count as RecordCountDateRange1,
    IT2.Record_Count as RecordCountDateRange2
FROM
    #ReqCat RC

with something that would "air code" like this:

SELECT
    RC.request_category, 
    IT1.Record_Count AS "@StDate1_to_@EnDate1",
    IT2.Record_Count AS "@StDate2_to_@EnDate2"
FROM
    #ReqCat RC

so that the field names would indicate the dates used in the comparison.

Is this possible to do without re-writing the whole procedure, and, if so, how would I do it?

Answer 1

Yes, this is possible by using dynamic SQL. I cannot test it but more or less it should work as you specified. It will show on screen (messages area) the sentence being run and you can adjust the SELECT @SQL = ... as per your convenience until you find your correct statement:

ALTER procedure [dbo].[sp_RequestCategoryCount]
    @StDate1 date,
    @EnDate1 date,
    @StDate2 date,
    @EnDate2 date
AS

BEGIN
DECLARE @SQL AS NVARCHAR(MAX);

SELECT distinct RQ.request_category
into #ReqCat
FROM ( Select distinct request_category from [VerInt_OneViewServiceReq]
         UNION ALL
       Select distinct request_category from [VerInt_OneViewServiceReq2]
       ) RQ


Select 
'1' as Iteration,
request_category,
Sum(1) as Record_Count
INTO #Iter1
from [VerInt_OneViewServiceReq]
where request_created_dt between @StDate1 and @EnDate1
group by request_category

--UNION ALL

Select 
'2' as Iteration,
request_category,
Sum(1) as Record_Count
INTO #Iter2
from [VerInt_OneViewServiceReq2]
where request_created_dt between @StDate2 and @EnDate2
group by request_category

--order by Iteration, request_category ASC

SELECT @SQL = '
Select RC.request_category, 
   IT1.Record_Count as ['+CONVERT(VARCHAR(10), @StDate1)+'_to_'+CONVERT(VARCHAR(10), @EnDate1)+'],
   IT2.Record_Count as ['+CONVERT(VARCHAR(10), @StDate2)+'_to_'+CONVERT(VARCHAR(10), @EnDate2)+']
from #ReqCat RC
LEFT JOIN #Iter1 IT1
  ON IT1.request_category = RC.request_category
LEFT JOIN #Iter2 IT2
  ON IT2.request_category = RC.request_category'
-- Debug purposes
PRINT @SQL;
EXEC sp_ExecuteSQL @SQL;

Drop Table #ReqCat
Drop Table #Iter1
Drop Table #Iter2

END