代码之家 › 专栏 › 技术社区 › Johnny Bones

使用动态列名创建SQL存储过程

stored-procedures tsql sql-server

Johnny Bones · 技术社区 · 5 年前

我有以下存储过程:

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

它所做的基本上是通过并排排列两个表中的两个时间跨度来比较,并且工作正常。

我要做的是把这个从上一个换下来 SELECT 声明:

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

有了这样的“空气代码”的东西:

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

这样,字段名将指示比较中使用的日期。

如果不重新编写整个过程,是否可以这样做?如果可以,我该怎么做?

1 回复 | 直到 5 年前

marc_s Anurag 5 年前

是的,这可以通过使用动态SQL来实现。我不能测试它,但或多或少它应该按您指定的方式工作。它将在屏幕(消息区)上显示正在运行的语句,您可以调整select@sql=…根据您的方便,直到您找到正确的声明:

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