參數化動態 TSQL - 動態參數

我們有一種情況，不同的客戶實際上要求相同的報告，但他們：

1. 不想要所有的列
2. 希望列的順序與我們自然擁有的順序不同
3. 希望它們被稱為不同於我們儲存方式的名稱（例如“客戶編號”與“客戶編號”）

目的是減輕適應這些定制請求所需的工作量。我們目前處於這些基本相同報告的數百個實例的位置（不包括這些表面差異）。我想看看我是否需要Dynamic每組這些基本查詢的一個實例，Parameter或者我是否可以parameter通過 1 處理所有可能的集合Stored Procedure。希望也不必擁有一堆特定SSRS RDL文件或SSIS DTSX包的特定實例來處理這些更改。數據將來自Stored Procedure我們需要顯示/呈現的數據。

讓我們假設我建構了一個Dynamic SQL Command輸出看起來像這樣的地方：

SELECT
Col1 AS 'Alias1',
Col2 AS 'Alias2',
Col3 AS 'Alias3'
FROM View
WHERE DateCol >= @StartDate
AND DateCol < @EndDate

它是由幾個不同的部分使用幾個表建構的。下面的表結構是更多的虛擬碼來理解這些想法，所以請忽略諸如沒有聲明主鍵之類的東西……

CREATE TABLE [report].[ReportTemplate]
(
   ID INT NOT NULL, --(Primary Key)
   ReportName VarChar(100) NOT NULL,
   ReportTypeID INT NOT NULL --(FK To report.ReportTemplateType.ID)
)

CREATE TABLE [report].[ReportTemplateType]
(
   ID INT NOT NULL, --(Primary Key)
   Name VarChar(50), --(Unique Constraint)
   BaseCommand VarChar(2000), --Holds FROM and JOIN clauses
   WhereCommand VarChar(2000), --Holds WHERE Clause
   WhereCommandParameters VarChar(2000), --Holds declaration of the parameters
)

CREATE TABLE [report].[ReportTemplateColumnDetails]
(
   ID INT NOT NULL, --(Primary Key)
   ReportTemplateID INT NOT NULL, --(FK to report.ReportTemplate.ID)
   ColumnName VarChar(256) NOT NULL,
   ColumnAlias VarChar(256) NULL, --Have logic handle blank vs NULL values
   ColumnOrder SmallInt NOT NULL
)

+----+-------------------+--------------+
| ID |    ReportName     | ReportTypeID |
+----+-------------------+--------------+
|  1 | Customer 1 Status |            1 |
|  2 | Customer 1 Sales  |            2 |
+----+-------------------+--------------+


+----+--------+-----------------+------------------------------------------------------------------------------+-------------------------------------------------------------------+
| ID |  Name  |   BaseCommand   |                                WhereCondition                                |                     WhereConditionParameters                      |
+----+--------+-----------------+------------------------------------------------------------------------------+-------------------------------------------------------------------+
|  1 | Status | FROM StatusView | WHERE DateCol >= @StartDate AND DateCol < @EndDate                           | @StartDate DATEIME, @EndDate DateTime                             |
|  2 | Sales  | FROM SalesView  | WHERE DateCol >= @StartDate AND DateCol < @EndDate AND Col4 = @TypeParameter | @StartDate DATEIME, @EndDate DateTime, @TypeParameter VarChar(20) |
+----+--------+-----------------+------------------------------------------------------------------------------+-------------------------------------------------------------------+

+----+------------------+------------+-------------+-------------+
| ID | ReportTemplateID | ColumnName | ColumnAlias | ColumnOrder |
+----+------------------+------------+-------------+-------------+
|  1 |                1 | Col1       | Alias1      |           1 |
|  2 |                1 | Col2       | Alias2      |           2 |
|  3 |                1 | Col3       | Alias3      |           3 |
|  4 |                2 | Col4       | Alias1      |           1 |
|  5 |                2 | Col5       | Alias2      |           2 |
|  6 |                2 | Col6       | Alias3      |           3 |
+----+------------------+------------+-------------+-------------+

該命令是使用以下程式碼建構的：

CREATE PROCEDURE [report].[ExecuteReportTemplate] (@ReportName VarChar(50))
AS
BEGIN
   DECLARE @SQLCommand VarChar(MAX) = 'SELECT ',
           @FirstColumnAdded BIT = 0,
           @BaseCommand VarChar(2000),
           @WhereCondition VarChar(2000),
           @WhereConditionParameters VarChar(2000)

   SELECT @BaseCommand = RTT.BaseCommand,
   @WhereCondition = RTT.WhereCommand, 
   @WhereConditionParameters = RTT.WhereCommandParameters 
   FROM [report].[ReportTemplateType] RTT
       INNER JOIN [report].[ReportTemplate] RT
           ON RTT.ID = RT.ReportTypeID
   WHERE RT.Name = @ReportName

   DECLARE @ColumnName VarChar(256),
           @ColumnAlias VarChar(256)

   DECLARE ColumnCursor CURSOR FOR
   SELECT ColumnName,
   ColumnAlias
   FROM [report].[ReportTemplateColumnDetails]
   ORDER BY ColumnOrder

   FETCH NEXT FROM ColumnCursor INTO @ColumnName, @ColumnAlias

   WHILE (@@FETCH_STATUS = 0)
   BEGIN
       --Add a comma inbetween columns, does not happen on the first one
       IF(@FirstColumnAdded = 1)
       BEGIN
           SET @SQLCommand = @SQLCommand + ', '
       END
       ELSE
       BEGIN
           SET @FirstColumnAdded = 1
       END

       --Adds the column into the list
       SET @SQLCommand = @SQLCommand + @ColumnName

       --If we have been provided an alias, set the alias
       IF(@ColumnAlias IS NULL OR LTRIM(RTRIM(@ColumnAlias)) = '')
       BEGIN
           @SQLCommand = @SQLCommand + 'AS ''' + @ColumnAlias + ''' '
       END
   END

   CLOSE ColumnCursor
   DEALLOCATE ColumnCursor

   --Now Add The Base Command
   SELECT @SQLCommand = @SQLCommand + ' ' + @BaseCommand + ' ' + @WhereCommand

   EXECUTE sp_executesql @sqlCommand, @WhereConditionParameters
       @StartDate = '2019-01-01', 
       @EndDate = GETDATE()
END

有沒有辦法動態更改配置和傳入的參數而無需建構單獨的命令？

我希望能夠用不同的and填充[report].[ReportTemplateType].[WhereCondition]and 。例如在類似的東西中添加第三個。我知道解決這個問題的唯一方法是創建一個不同的地方，其中所有內容都與上述相同，但我們會將最後一塊更改為：[report].[ReportTemplateType].[WhereCondition]``WHERE``Parameters``column``WHERE condition``Col4 = @TypeParameter``Stored Procedure``Stored Procedure

EXECUTE sp_executesql @sqlCommand, @WhereConditionParameters
       @StartDate = '2019-01-01', 
       @EndDate = GETDATE(),
       @TypeParameter = 'SomeStringValue'

有沒有辦法動態更改配置和傳入的參數而無需建構單獨的命令？

評論太長了，所以我把它拋給了一個答案。

我個人的偏好是動態 SQL (DSQL) 不應該被非管理性質的生產程式碼使用。在管理環境時，我一直使用 DSQL，但這些都不需要任何顯著水平的性能。當您開始將 Dynamic SQL 作為生產程式碼推出時，您將不可避免地遇到性能問題。在那一刻，輪子脫落了，因為 DSQL 是出了名的故障排除。顯然，這只是一種意見，你可以自由地做你想做的事，但我強烈反對在你推送到生產的任何程式碼中使用 DSQL。

在你走得更遠之前，我建議你閱讀一下我認為是 Erland Sommarskog 撰寫的關於動態 SQL 的權威文章：動態 SQL的詛咒和祝福

這真是一本好書；請注意，消化這一切需要一點時間。

我現在要跳下我的肥皂盒……

至於你的問題：

有沒有辦法動態更改配置和傳入的參數而無需建構單獨的命令？

是的，您可能需要嵌套您的 DSQL或相應地添加額外的報告表，但只有您的要求和方法才能決定適當的路徑。 如果你是更緊迫的問題，顯然我會說不。

引用自：https://dba.stackexchange.com/questions/242755