sql - 将 Oracle 查询转换为 SQL Server Reporting Services (SSRS) 的存储过程

Question

我怎样才能将这个查询转换为一个存储过程，它也返回相同的(字段)表:

SELECT   R.REGION_NAME,
           TP.TERRITORY_NAME,
           LSC.LOOKUP_NAME,
           CAST (WM_CONCAT (CM.CUST_NAME) AS VARCHAR2 (500)) AS CUST_NAMES,
           TP.VIEWER_PROFILE,
           TP.CASTING_PREFERENCE,
           TP.PROG_TYPE_PREFERENCE,
           STN_LIST.STN_NAMES,
           STN_LIST.LIC_RIGHTS
    FROM               TERRITORY_PROFILE_MSTR TP
                    INNER JOIN
                       REGION_MSTR R
                    ON TP.REGION_ID = R.REGION_ID
                 LEFT OUTER JOIN
                    LOOKUP_SUB_CATEGORY LSC
                 ON TP.BROADCAST_STD_CD = LSC.LOOKUP_SUB_CAT_ID
              LEFT OUTER JOIN
                 CUST_MSTR CM
              ON CM.TERRITORY_ID = TP.TERRITORY_ID
           LEFT OUTER JOIN
              (SELECT   CAST (WM_CONCAT (SLR.CUST_STN_NAME) AS VARCHAR2 (500))
                             AS STN_NAMES,
                          CAST (WM_CONCAT (LRM.LIC_RIGHTS_ID) AS VARCHAR2 (500))
                             AS LIC_RIGHTS,
                          SLR.CUST_COMPETITOR_ID AS CUST_ID
                   FROM   STN_LIC_RIGHTS SLR, LIC_RIGHTS_MSTR LRM
                  WHERE   SLR.STN_LIC_ID = LRM.LIC_RIGHTS_ID
               GROUP BY   SLR.CUST_COMPETITOR_ID) STN_LIST
           ON STN_LIST.CUST_ID = CM.CUST_ID
   WHERE   TP.TERRITORY_ID <> 0
GROUP BY   TP.TERRITORY_NAME,
           R.REGION_NAME,
           LSC.LOOKUP_NAME,
           STN_LIST.STN_NAMES,
           STN_LIST.LIC_RIGHTS,
           TP.VIEWER_PROFILE,
           TP.CASTING_PREFERENCE,
           TP.PROG_TYPE_PREFERENCE
ORDER BY   R.REGION_NAME, TP.TERRITORY_NAME

我想我不需要指定表结构。请注意，查询功能齐全，并且值以我期望的方式正确返回。

谢谢。

更新:

存储过程必须兼容并可通过 SQL Server Reporting Services 或 SSRS 在 ASP.NET(C#) 中执行。

Answer 1

有几种方法可以完成您的要求:

1. 通过使用返回 sys_refcursor 的函数/过程，如 here 所述
2. 通过使用描述的“参数化 View ”here
3. 通过使用描述的表函数 here

可能还有更多...