Dynamic SQL without Dynamic SQL?

  • Sorry for a bit confusing topic, but need your advise, please.

    I have old good stored procedure, doing quite complex SELECT FOR XML and all is perfect.

    that SELECT has rows, like:

    CASE RTRIM(a.Reference2) WHEN '' THEN RTRIM(b.Reference_2) ELSE RTRIM(a.Reference2) END AS "@crewnumber",

    CASE RTRIM(a.Reference3) WHEN '' THEN RTRIM(b.Reference_3) ELSE RTRIM(a.Reference3) END AS "@travelrefid"

    Now a new customer require flexibility for the above two columns. They want to select per each of their clients which of the references will be @crewnumber and which - @travelrefid. So there is now a screen, allowing to select a specific reference from a combo-box (list of references runs from reference 1 to reference 10).

    My first thought was to change it to dynamic SQL, but again - the SQL is very complex already.

    My other idea is to add another CASE now, something like:

    DECLARE @selected_column VARCHAR(30)

    CASE WHEN @selected_column = 'Reference2'

    THEN CASE RTRIM(a.Reference2) WHEN '' THEN RTRIM(b.Reference_2) ELSE RTRIM(a.Reference2) END

    WHEN @selected_column = 'Reference3'

    THEN CASE RTRIM(a.Reference3) WHEN '' THEN RTRIM(b.Reference_3) ELSE RTRIM(a.Reference3) END

    ..... and so on eight more times (again, it is possible to select between reference 1 and reference 10).

    I wonder if one can advise much more elegant way to deal with it, please?

    Thank you all

  • Sounds similar to a "catch-all" query. Have a read through this (http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/) blog post[/url]. There are many other posts on the topic.


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Thank you Craig,

    Sadly this blog deals with WHERE part, where my issue is with the columns I select to present.

  • BOR15K (9/22/2015)


    Sorry for a bit confusing topic, but need your advise, please.

    I have old good stored procedure, doing quite complex SELECT FOR XML and all is perfect.

    that SELECT has rows, like:

    CASE RTRIM(a.Reference2) WHEN '' THEN RTRIM(b.Reference_2) ELSE RTRIM(a.Reference2) END AS "@crewnumber",

    CASE RTRIM(a.Reference3) WHEN '' THEN RTRIM(b.Reference_3) ELSE RTRIM(a.Reference3) END AS "@travelrefid"

    Now a new customer require flexibility for the above two columns. They want to select per each of their clients which of the references will be @crewnumber and which - @travelrefid. So there is now a screen, allowing to select a specific reference from a combo-box (list of references runs from reference 1 to reference 10).

    My first thought was to change it to dynamic SQL, but again - the SQL is very complex already.

    My other idea is to add another CASE now, something like:

    DECLARE @selected_column VARCHAR(30)

    CASE WHEN @selected_column = 'Reference2'

    THEN CASE RTRIM(a.Reference2) WHEN '' THEN RTRIM(b.Reference_2) ELSE RTRIM(a.Reference2) END

    WHEN @selected_column = 'Reference3'

    THEN CASE RTRIM(a.Reference3) WHEN '' THEN RTRIM(b.Reference_3) ELSE RTRIM(a.Reference3) END

    ..... and so on eight more times (again, it is possible to select between reference 1 and reference 10).

    I wonder if one can advise much more elegant way to deal with it, please?

    Thank you all

    First, let's start off with some sample data. (Please see the link in my signature for "For better assistance in answering your questions" for how to do this yourself. Also, please remember that since we are all volunteers, many helpers here won't bother to do this, so you will be helping yourself to post this.)

    DECLARE @TestTable1 TABLE (

    RowID INTEGER IDENTITY PRIMARY KEY CLUSTERED,

    Reference1 VARCHAR(10),

    Reference2 VARCHAR(10),

    Reference3 VARCHAR(10),

    Reference4 VARCHAR(10),

    Reference5 VARCHAR(10),

    Reference6 VARCHAR(10),

    Reference7 VARCHAR(10),

    Reference8 VARCHAR(10),

    Reference9 VARCHAR(10),

    Reference10 VARCHAR(10));

    DECLARE @TestTable2 TABLE (

    RowID INTEGER IDENTITY PRIMARY KEY CLUSTERED,

    Reference1 VARCHAR(10),

    Reference2 VARCHAR(10),

    Reference3 VARCHAR(10),

    Reference4 VARCHAR(10),

    Reference5 VARCHAR(10),

    Reference6 VARCHAR(10),

    Reference7 VARCHAR(10),

    Reference8 VARCHAR(10),

    Reference9 VARCHAR(10),

    Reference10 VARCHAR(10));

    INSERT INTO @TestTable1

    (Reference1,

    Reference2,

    Reference3,

    Reference4,

    Reference5,

    Reference6,

    Reference7,

    Reference8,

    Reference9,

    Reference10

    )

    VALUES ('1', -- Reference1 - varchar(10)

    '2', -- Reference2 - varchar(10)

    '3', -- Reference3 - varchar(10)

    '', -- Reference4 - varchar(10)

    '', -- Reference5 - varchar(10)

    '6', -- Reference6 - varchar(10)

    '7', -- Reference7 - varchar(10)

    '8', -- Reference8 - varchar(10)

    '', -- Reference9 - varchar(10)

    '' -- Reference10 - varchar(10)

    );

    INSERT INTO @TestTable2

    (Reference1,

    Reference2,

    Reference3,

    Reference4,

    Reference5,

    Reference6,

    Reference7,

    Reference8,

    Reference9,

    Reference10

    )

    VALUES ('1', -- Reference1 - varchar(10)

    '2', -- Reference2 - varchar(10)

    '', -- Reference3 - varchar(10)

    '4', -- Reference4 - varchar(10)

    '5', -- Reference5 - varchar(10)

    '', -- Reference6 - varchar(10)

    '', -- Reference7 - varchar(10)

    '8', -- Reference8 - varchar(10)

    '9', -- Reference9 - varchar(10)

    '' -- Reference10 - varchar(10)

    );

    From my understanding of your requirements, you want to be able to specify which of the ReferenceX columns to use for each the CrewNumber and the TravelRefID columns. And you want this a bit simpler. So we start off by making a variable for each of those columns.

    The next major thing that we do is use CROSS APPLY VALUES to unpivot the data into multiple rows to make it easier to work with.

    Then we go back to the SELECT clause, and add in our calculations for each column. I've changed your complicated case statement into a ISNULL / NULLIF nested expression. This expression is only used if the specified column (specified in the variable) matches the column reference id in the CROSS APPLY VALUES list, otherwise NULL is used. This is performed for each of the columns. Finally, these are aggregated together with the MAX function, and grouped by the RowID.

    DECLARE @crewnumber VARCHAR(11) = 'Reference3',

    @travelrefid VARCHAR(11) = 'Reference2';

    SELECT a.RowID,

    MAX(CASE WHEN ca.ReferenceID = @crewnumber THEN ISNULL(NULLIF(RTRIM(ca.ReferenceA),''), RTRIM(ca.ReferenceB)) ELSE NULL END) AS CrewNumber,

    MAX(CASE WHEN ca.ReferenceID = @travelrefid THEN ISNULL(NULLIF(RTRIM(ca.ReferenceA),''), RTRIM(ca.ReferenceB)) ELSE NULL END) AS TravelRefID

    FROM @TestTable1 a

    JOIN @TestTable2 b ON a.RowID = b.RowID

    CROSS APPLY (VALUES ('Reference1', a.Reference1, b.Reference1),

    ('Reference2', a.Reference2, b.Reference2),

    ('Reference3', a.Reference3, b.Reference3),

    ('Reference4', a.Reference4, b.Reference4),

    ('Reference5', a.Reference5, b.Reference5),

    ('Reference6', a.Reference6, b.Reference6),

    ('Reference7', a.Reference7, b.Reference7),

    ('Reference8', a.Reference8, b.Reference8),

    ('Reference9', a.Reference9, b.Reference9),

    ('Reference10', a.Reference10, b.Reference10)

    ) ca(ReferenceID, ReferenceA, ReferenceB)

    GROUP BY a.RowID;

    Easy peasy, make my pizza cheesy. 😀

    Does this do what you desire?

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Thank you Wayne.

    I will try to apply your suggestion to my current logic and see what comes out.

    With regards to posting examples, I didn't write one, as didn't expect a solution, but suggestion / guidelines only.

    Much appreciated.

  • You may unpivote fixed number of columns and select specified col value without dynamic SQL

    declare @t table (

    id int identity

    ,ref01 varchar(10)

    ,ref02 varchar(10)

    ,ref03 varchar(10)

    ,ref04 varchar(10)

    ,ref05 varchar(10)

    ,ref06 varchar(10)

    );

    insert @t values ('a-ref01','a-ref02','a-ref03','a-ref04','a-ref05','a-ref06')

    ,('b-ref01','b-ref02','b-ref03','b-ref04','b-ref05','b-ref06');

    --

    declare @selected_column VARCHAR(30) = 'ref04';

    select id, val_of_selected_col

    from @t

    cross apply (select val as val_of_selected_col from (values

    ('ref01',ref01)

    ,('ref02',ref02)

    ,('ref03',ref03)

    ,('ref04',ref04)

    ,('ref05',ref05)

    ,('ref06',ref06)

    ) unpvt (name,val)

    where name = @selected_column ) x

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply