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

    Question

  • 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:

    StringTarget,StringKey,StringValue
    Misc,LegalEntity,Acme Corp.
    Sales,SomeNumber,999
    Sales,SomeOtherNumber,555

    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 )
    AS
    BEGIN
    @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;
    END;

    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

Answers

  • 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 :

    https://dzone.com/articles/azure-data-lake-with-u-sql-using-c-code-behind

    Hope this helps.


    MSDN

    Thursday, February 7, 2019 9:04 AM
    Moderator