Looking for approach in efficiently using config or other file-based values in a SELECT statement


  • I presently have a stored procedure with a statement that writes-out a file like this with two hard-coded constants:

    @rowset =
    SELECT   [p].*
            ,"Acme Corp." AS [LegalEntity]
            ,"555" AS [SomeOtherNumber]
    FROM    @people AS [p]

    However, these values may need to be changed by the business, and I do not want to have to modify the procedure, re-compile and re-deploy out to the Data Lake environment, especially if it's in production.

    My thought is to have a "config" or "strings" file looking like this:

    Misc,LegalEntity,Acme Corp.

    I want to EXTRACT this file for the values, I've written a TVF to retrieve like:

    CREATE FUNCTION dbo.fn_Get_ValueString
    ( @stringTarget string, @stringKey string )
    RETURNS @result TABLE ([StringValue] string )
    @result = 
        SELECT  [StringValue] 
        FROM (
                EXTRACT [StringTarget] string 
                       ,[StringKey] string 
                       ,[StringValue] string 
                FROM "configfiles/ValueStrings.csv"
                USING Extractors.Text(delimiter: ',', skipFirstNRows: 1, encoding: Encoding.UTF8, quoting: true, silent: false)
             ) AS [a]
        WHERE   [a].[StringTarget] == @stringTarget
        AND     [a].[StringKey] == @stringKey;

    and to extract this works:

    @rowset1 =
    SELECT   [p].*
            ,[f1].StringValue AS [LegalEntity]
            ,[f2].StringValue AS [SomeOtherNumber]
    FROM    @people AS [p]
            CROSS JOIN dbo.fn_Get_ValueString("Misc","LegalEntity") AS [f1]
            CROSS JOIN dbo.fn_Get_ValueString("Sales","SomeOtherNumber") AS [f2]

    However, it seems clunky and awkward to CROSS JOIN multiple times for each string I want, say I need five strings I'd have five CROSS JOINS. (I know I could also construct this using Derived Tables to JOIN to, but still would need this five times)

    Any ideas of how to "flatten" this or "pivot" so that I can get the values I need with just ONE Join?  Would I need to write some code-behind in C#?  I thought perhaps a SQL.ARRAY or SQL.MAP could be used, however I'm not seeing it.  I want some flexibility to store the file in a name-value pairs format like I have shown.

    Thank you in advance for any ideas!

    Bill Blakey

    Wednesday, January 23, 2019 11:03 PM


  • Hi Bill,

    Sorry for the delayed response. I can think of one way to do it using C# code behind the scenes.

    I found a blog that might be useful in your case :

    Hope this helps.


    Thursday, February 7, 2019 9:04 AM