1

We have a situation where effectively the same report is requested by different customers but they:

  1. Don't want all the columns
  2. Want the columns in a different order than we naturally have them
  3. Want them called something different than how we have is stored ("Customer No" vs "Customer Number" for example)

The intent is to ease the amount of effort needed accommodate these customization requests. We are currently in a position with a couple hundred instances of these basically identical reports (excluding these superficial differences). I am looking to see if I needed once instance of these base Dynamic Queries per Parameter set or if I could handle all possible parameter sets via 1 Stored Procedure. The hope is to also not have to have a bunch of specific instances of some kind of SSRS RDL file or an SSIS DTSX package to handle these changes. That the data would come out of the Stored Procedure as we need it to be displayed/presented.

Lets assume I build out a Dynamic SQL Command where the output looks something like:

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

It is built from a couple different parts using a couple of tables. Below table structures are more pseudo code to get the ideas across, so please ignore things like there are no Primary Keys declared, etc...

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 |
+----+------------------+------------+-------------+-------------+

The command is built using code below:

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

Is there a way to dynamical change the parameters that are configured and passed in without having to build a separate command?

I would like to be able to populate [report].[ReportTemplateType].[WhereCondition] and [report].[ReportTemplateType].[WhereCondition] with a different WHERE and Parameters. For example adding a 3rd column in the WHERE condition something like Col4 = @TypeParameter. The only way I know to solve this is to create a different Stored Procedure where everything is identical to the above Stored Procedure but we would change the last piece to:

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

Is there a way to dynamical change the parameters that are configured and passed in without having to build a separate command?

0

3 Answers 3

2

Too long for a comment, so I've dumped this down to an answer.

My personal preference is that Dynamic SQL (DSQL) should never be used by production code that is not administrative in nature. I use DSQL all the time when it comes to managing environments, but none of these truly require any significant level of performance. The moment you start pushing Dynamic SQL out as production code you will inevitably run into performance issues. At that moment, the wheels come off because DSQL is notorious to troubleshoot. Obviously, that's just an opinion and you're free to do what you want, but I would heavily discourage the use of DSQL in any code you push to production.

Before you go any further down this road, I would recommend reading, what I feel is the definitive article on Dynamic SQL, by Erland Sommarskog: The Curse and Blessings of Dynamic SQL

It's a really good read; just be warned it will take a little time to digest it all.

I'll jump off my soapbox now....

As for your question:

Is there a way to dynamical change the parameters that are configured and passed in without having to build a separate command?

Yes, you may need to nest your DSQL or add additional report tables accordingly, but only your requirements and approach will dictate the appropriate path. Should you is the more pressing question, and obviously I would say no.

0
1

From comments:

I think you need to take a step back and look at what you want to actually achieve with this. You are walking down a path with many known issues. For troubleshooting, performance, maintainability and many others I would advise you to just create multiple stored procedures for each type of query you want to compile and run those. These types of SQL Fragment tables are going to lead you to a bad end as the next poor soul to inherit this will wish bad karma against you. – Jonathan Fite

I agree with @JonathanFite. I have gone down this path before and in the end you eventually are forced to create multiple procedures anyways. Given you last comment, I would say you are trying to do this in the wrong area of your stack. Instead of trying to make the queries flexible enough to handle all situations, I would suggest that you spend the time building or using an existing reporting solution that allows users to customize the way the reports look (and save the customization). So if the want to change the column names, order, display, etc.. they can do it themselves. – camba1

0

It is ok to use dynamic Sql as far as it come within "Blessing Part of Dynamic Sql".

I am not getting much into it.

Your requirement in Small scale,

CREATE TABLE #UserPrefReport
(Userid       INT NOT NULL, 
 Reportid     INT NOT NULL, 
 TableorView  VARCHAR(100) NOT NULL, 
 AliasColumns VARCHAR(5000) NOT NULL, 
 OrderBy      VARCHAR(100)
);
INSERT INTO #UserPrefReport
(Userid, 
 Reportid, 
 TableorView, 
 AliasColumns, 
 OrderBy
)
VALUES
(1, 
 1, 
 'DimCustomer', 
 'FirstName as [First Name],MiddleName as [Middle Name]', 
 'firstname asc, MiddleName desc'
),
(2, 
 1, 
 'DimCustomer', 
 'FirstName as [FName],MiddleName as [MName]', 
 'firstname asc, MiddleName desc'
);

There will be UI where you will populate UserPrefReport.There should not be any bug while populating.you will populate value like 'FirstName as [FName],MiddleName as [MName]' in same order as it is desire by User.

currently there is no use of Reportid.Or you can create another table for TableorView and reference Reportid here.It is not big deal.

DECLARE @Userid INT= 1;-- parameter of SP
DECLARE @TableorView VARCHAR(100)= 'DimCustomer';-- parameter of SP
-- Local variable of SP below
    DECLARE @OrderBy VARCHAR(100);
    DECLARE @Sql NVARCHAR(MAX)= '';
    DECLARE @Cols VARCHAR(MAX)= '';

    SELECT @Cols = AliasColumns, 
           @OrderBy = OrderBy
    FROM #UserPrefReport
    WHERE userid = @Userid
          AND TableorView = @TableorView;
    SET @Sql = N'select ' + @Cols + ' from ' + @TableorView + ' order by ' + @OrderBy + '';
    PRINT @Sql;
    EXEC sp_executesql 
         @Sql;
    DROP TABLE #UserPrefReport;

Your requirement in Big Scale,

This mean that you are also giving user facility to search on those fields.And only those fields should be populated.

In that case design of UserPrefReport will be in this manner,

CREATE TABLE #UserPrefReport1
(Userid       INT NOT NULL, 
 TableorView  VARCHAR(100) NOT NULL, 
 colname      VARCHAR(100) NOT NULL, 
 AliasColumns VARCHAR(100) NOT NULL
);
INSERT INTO #UserPrefReport1
(Userid, 
 TableorView, 
 colname, 
 AliasColumns
)
VALUES
(1, 
 'DimCustomer', 
 'FirstName', 
 '[First Name]'
),
(1, 
 'DimCustomer', 
 'MiddleName', 
 '[Middle Name]'
),
(2, 
 'DimCustomer', 
 'FirstName', 
 '[FName]'
),
(2, 
 'DimCustomer', 
 'MiddleName', 
 '[MName]'
);
SELECT *
FROM #UserPrefReport1;

DROP TABLE #UserPrefReport1;

In both case population of UserPrefReport is very important.

Tell me what part of your requirement I am missing in my script ??

2
  • Thank you for your response. It is cool to see the design where all the columns are just a single column in the table and not a child table. So what we are looking to do is use Dynamic Parameterized SQL. The challenge is when we assign the values to the parameters, they can't be a single string but a comma separated list of objects. So we were going to have to build that list of objects separately. I didn't know how to create that without exposing ourselves to SQL Injection attacks. Unfortunately with this answer I didn't see anything for configuring dynamically generated parameters. Jul 15, 2019 at 13:00
  • @KirkSaunders,All the columns are just a single column because for now I have not understood your complete requirement, and that was just an idea to start with.Nobody will write that big thing without actually understanding your requirement and how you are storing data.This part is not clear to me The challenge is when we assign the values to the parameters, they can't be a single string but a comma separated list of objects. So we were going to have to build that list of objects separately.
    – KumarHarsh
    Jul 16, 2019 at 4:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.