0

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?

  • how many levels of dynamic T-SQL are you willing to go? After about three levels, the single quotes get out of hand. Essentially, you just have to build dynamic T-SQL that builds dynamic T-SQL, etc. – Max Vernon Jul 12 at 13:30
  • @MaxVernon I don't anticipate the queries would get very complicated, so I think I am willing to go a couple of layers in. My main concern is loosing the protection from SQL Injection. We won't get any input from an end user directly but from IT Resource configuration on a table so I may be looking for a more complicated solution than I actually need. – Kirk Saunders Jul 12 at 13:35
  • 3
    It'll be easier for us to provide an answer if you can mock up a more complete minimal reproducible example – Max Vernon Jul 12 at 13:40
  • Why would you want to change the parameters? Make yourself an exhaustive list of parameters and send them all the time either set or if you do not want them with a default value. Let the dynamic SQL decide if you use them or not. If you do not use them they will just get ignored. – Dumitrescu Bogdan Jul 12 at 13:44
  • @DumitrescuBogdan I would like for this to handle situations through only data driven configuration. We could always use a single command with all possible values provided, we would just need to make SP code changes to support new variables. If I can avoid that it would be very nice. – Kirk Saunders Jul 12 at 13:48
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.

  • 1
    +1 I'll join you on the soapbox. I'll only ever do very basic dynamic SQL when necessary for non-administrative code in rare circumstances. Opening that door also often ends up with developers going mad in their effort to write One Query To Rule Them All, in my experience. – LowlyDBA Jul 12 at 15:56
  • 1
    OP is committing another sin, and that's having a stored procedure with a non-deterministic result set (and potentially multiple, depending). – Jonathan Fite Jul 12 at 16:06
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 ??

  • 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. – Kirk Saunders Jul 15 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 at 4:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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