Lookup and insert into column without dynamic SQL?

  • Hi all,

    99% sure the answer to this question is "it is not possible", but figured I'd check anyway.

    Suppose I have the following schema:

    CREATE TABLE #FieldValues
    (
    ID INT IDENTITY PRIMARY KEY,
    Field1 VARCHAR(500),
    Field2 VARCHAR(500),
    Field3 VARCHAR(500)
    )

    CREATE TABLE #FieldDefinitions
    (
    ID INT IDENTITY PRIMARY KEY,
    DefinitionName VARCHAR(50),
    FieldName VARCHAR(50)
    )

    INSERT INTO #FieldDefinitions (DefinitionName, FieldName)
    VALUES ('Def1', 'Field1')

    INSERT INTO #FieldDefinitions (DefinitionName, FieldName)
    VALUES ('Def2', 'Field2')

    INSERT INTO #FieldDefinitions (DefinitionName, FieldName)
    VALUES ('Def3', 'Field3')

    INSERT INTO #FieldValues (Field3)
    SELECT 'I inserted this manually, but would like to make this query adaptable'

    My objective is to do that insert at the end, except by querying the #FieldDefinitions table to discover the actual column I want to insert into. I know I can do this with dynamic SQL, using sp_ExecuteSQL. I would like to know if there is any way I can do this *without* resorting to using sp_ExecuteSQL (or EXEC, or other dynamic SQL approaches).

    I know that sys.columns stores the column definitions on the table. I know I can query that table with the field name I'm looking for, based on a lookup from #FieldDefinitions. Is there any way to construct the INSERT statement using, for example, OBJECT_ID or OBJECT_NAME with the column name I find?

  • It would appear that you have a table full of generic column names and that you're using a different table to define what those column names should be or used as.  If that's true, I think that you're in for a world of hurt for both code complexity and data integrity.  Even an EAV would work better, IMHO.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Indeed. Sadly, I have no control over this. We're using a third-party application, and I'm retrofitting functionality onto it.

    On the plus side, this is purely for auditing, so there shouldn't be any real concerns regarding performance / etc. The question is more academic than anything else.

  • I'm confused about a couple of things.

    First, why do you only want to INSERT "field3" (really column3)?

    There's no link or association to #FieldValues.  Surely there must be some columns missing from that table?

     

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Short answer? 'Cause that's the way the third-party designed their schema / software :/

    The #FieldValues table has a whole pile of columns (String_00, String_01, String_02, etc...), while the #FieldDefinitions table stores which column will have specific values inserted into.

    So, for example, there might be a field definition named "Weather", whose value "Cloudy" for a specific instance is stored in the field String_01. There might be another field definition named "Temperature", whose value is "30 degrees" is to be stored in String_03. Inserting into that table, I would INSERT INTO (String_01, String_03) VALUES ('Cloudy', '30 degrees').

    What I'm essentially reduced to, is script that is similar to:

    DECLARE @WeatherFieldName VARCHAR(200) 
    DECLARE @TemperatureFieldName VARCHAR(200)

    SELECT
    @WeatherFieldName = FieldName
    FROM #FieldDefinitions
    WHERE DefinitionName = 'Weather'

    SELECT
    @TemperatureFieldName = FieldName
    FROM #FieldDefinitions
    WHERE DefinitionName = 'Temperature'

    DECLARE @Query VARCHAR(MAX)
    SET @Query =
    'INSERT INTO #FieldValues (' + @WeatherFieldName + ', ' + @TemperatureFieldName + ')
    VALUES (''Cloudy'', ''30 degrees'')'

    EXEC (@Query)
  • The answer is "yes" (remember the questions is "can I"), but definitely not sure that you would want to - it would also depend on how many columns are in the table.

    If you only have a few columns this can work, but I know there are tables out there the hundreds if not thousands and do this which this would be completely unworkable.

    CREATE TABLE #FieldValues
    (
    ID INT IDENTITY PRIMARY KEY,
    Field1 VARCHAR(500),
    Field2 VARCHAR(500),
    Field3 VARCHAR(500)
    )

    CREATE TABLE #FieldDefinitions
    (
    ID INT IDENTITY PRIMARY KEY,
    DefinitionName VARCHAR(50),
    FieldName VARCHAR(50)
    )

    INSERT INTO #FieldDefinitions (DefinitionName, FieldName)
    VALUES ('Weather', 'Field1')

    INSERT INTO #FieldDefinitions (DefinitionName, FieldName)
    VALUES ('Def2', 'Field2')

    INSERT INTO #FieldDefinitions (DefinitionName, FieldName)
    VALUES ('Temperature', 'Field3')

    DECLARE @WeatherFieldName VARCHAR(200) = 'Weather';
    DECLARE @TemperatureFieldName VARCHAR(200) = 'Temperature';

    DECLARE @WeatherValue VARCHAR(200) = 'Cloudy';
    DECLARE @TemperatureValue VARCHAR(200) = '30 degrees';

    INSERT INTO #FieldValues (Field1, Field2, Field3)
    SELECT (SELECT MAX(CASE
    WHEN FieldName = 'Field1' AND DefinitionName = @WeatherFieldName THEN @WeatherValue
    WHEN FieldName = 'Field1' AND DefinitionName = @TemperatureFieldName THEN @TemperatureValue
    else null
    end)
    FROM #FieldDefinitions
    WHERE DefinitionName in (@WeatherFieldName, @TemperatureFieldName)),
    (SELECT MAX(CASE
    WHEN FieldName = 'Field2' AND DefinitionName = @WeatherFieldName THEN @WeatherValue
    WHEN FieldName = 'Field2' AND DefinitionName = @TemperatureFieldName THEN @TemperatureValue
    else null
    end)
    FROM #FieldDefinitions
    WHERE DefinitionName in (@WeatherFieldName, @TemperatureFieldName)),
    (SELECT MAX(CASE
    WHEN FieldName = 'Field3' AND DefinitionName = @WeatherFieldName THEN @WeatherValue
    WHEN FieldName = 'Field3' AND DefinitionName = @TemperatureFieldName THEN @TemperatureValue
    else null
    end)
    FROM #FieldDefinitions
    WHERE DefinitionName in (@WeatherFieldName, @TemperatureFieldName));

    select @WeatherFieldName, @WeatherValue, @TemperatureFieldName, @TemperatureValue;

    select * from #FieldDefinitions;
    select * from #FieldValues;

    drop TABLE #FieldValues;
    drop TABLE #FieldDefinitions;

     

    As a side note - I would be happier if the #FieldDefinitions table had "FieldName" as the PRIMARY KEY, because it would stop the filed from being used for two different values and therefore having a possible clash e.g. resulting in INSERT INTO #Tbl (Field1,Field1).

     

  • The tables and fields are purely for demonstration purposes. The actual table definitions and field names are a bit more sensible (though not much more so).

    As for your proposed solution - I think it's probably a better one than mine if the schema had only a limited number of columns. Unfortunately in my case there are literally hundreds of them in the table (String_01, String_02, Date_01, etc...), and so writing it in your way would take up considerably more space.

    Still - while the code would definitely *look* ugly, I wonder if ultimately it would do a better job? It does remove the need for dynamic SQL, and avoids the problems of SQL injection / etc..., although probably at the cost of performance, since you're querying the definitions table one time for each column name.

  • Are you genuinely on SQL 2016 or higher?

    If so, I think CHOOSE should save you a lot of coding here.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • @kramaswamy I agree that there would be some performance hit on this, but it shouldn't be much especially if both of the data columns have unique indexes.  Being *small* it is likely to remain cached and reused for your query anyway.

     

    @scottpletcher - I had a quick look at the CHOOSE function, but as it is an indexed lookup into the remaining fields and I couldn't work out how you would use it without having the index key.  Could you supply an example?

  • A small perf improvement on @shane.green code

    INSERT INTO #FieldValues ( Field1, Field2, Field3 )
    SELECT Field1 = MAX( CASE
    WHEN FieldName = 'Field1' AND DefinitionName = @WeatherFieldName THEN @WeatherValue
    WHEN FieldName = 'Field1' AND DefinitionName = @TemperatureFieldName THEN @TemperatureValue
    ELSE NULL
    END )
    , Field2 = MAX( CASE
    WHEN FieldName = 'Field2' AND DefinitionName = @WeatherFieldName THEN @WeatherValue
    WHEN FieldName = 'Field2' AND DefinitionName = @TemperatureFieldName THEN @TemperatureValue
    ELSE NULL
    END )
    , Field3 = MAX( CASE
    WHEN FieldName = 'Field3' AND DefinitionName = @WeatherFieldName THEN @WeatherValue
    WHEN FieldName = 'Field3' AND DefinitionName = @TemperatureFieldName THEN @TemperatureValue
    ELSE NULL
    END )
    FROM #FieldDefinitions
    WHERE DefinitionName IN ( @WeatherFieldName, @TemperatureFieldName );
  • @desnorton - yeah that does seem like it should work better. Still will look rather ugly when dealing with hundreds of columns though.

    @scottpletcher - I'll take a look at CHOOSE, hadn't seen it before.

    • This reply was modified 4 weeks, 1 day ago by  kramaswamy.
  • Having a large list of fields, you are definitely going to have some ugly long code.

    If SQL Injection is your only objection to Dynamic SQL, then you can mitigate the risk by using sp_executesql instead of EXEC.

    ---=====================================================
    ---== Input parameters - This is where the possible SQL injection comes from
    ---=====================================================
    DECLARE @inParm_WeatherValue varchar(500) = 'Cloudy';
    DECLARE @inParm_TemperatureValue varchar(500) = '30 degrees';
    ---=====================================================

    ---=====================================================
    ---== Here we are internal, and building up a list of fields from our config.
    ---=====================================================

    DECLARE @WeatherFieldName varchar(200)
    , @TemperatureFieldName varchar(200)
    , @Query varchar(MAX);

    SELECT @WeatherFieldName = MAX( CASE WHEN DefinitionName = 'Weather' THEN FieldName ELSE NULL END )
    , @TemperatureFieldName = MAX( CASE WHEN DefinitionName = 'Temperature' THEN FieldName ELSE NULL END )
    FROM #FieldDefinitions
    WHERE DefinitionName IN ( 'Weather', 'Temperature' );



    SET @Query = 'INSERT INTO #FieldValues ( ' + @WeatherFieldName + ', ' + @TemperatureFieldName + ' )
    VALUES ( @WeatherValue, @TemperatureValue )';

    ---=====================================================
    ---== By using sp_executesql, we properly parameterise the
    ---== Dynamic SQL, thereby eliminating the SQL Injection Risk.
    ---=====================================================
    EXEC sys.sp_executesql @stmt = @Query
    , @params = N'@WeatherValue varchar(500), @TemperatureValue varchar(500)'
    , @WeatherValue = @inParm_WeatherValue
    , @TemperatureValue = @inParm_TemperatureValue;
  • @desnorton - I don't think sp_ExecuteSQL would actually diminish the danger. If the @WeatherFieldName variable had a semicolon in it, followed by a query - I believe it would still be executed.

  • kramaswamy wrote:

    @DesNorton - I don't think sp_ExecuteSQL would actually diminish the danger. If the @WeatherFieldName variable had a semicolon in it, followed by a query - I believe it would still be executed.

    Indeed, both EXEC (@SQL) and EXEC sys.sp_executesql are both as as open to injection as the other. The latter, however, allows for parametrisation, hugely diminishing the risk of injection if used correctly; EXEC (@SQL) can't be parametrised so is seen as simply able to suffer injection attacks far more easily. Then, provided you properly quote any dynamic object names (using QUOTENAME) you should be safe from injection.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • kramaswamy wrote:

    @DesNorton - I don't think sp_ExecuteSQL would actually diminish the danger. If the @WeatherFieldName variable had a semicolon in it, followed by a query - I believe it would still be executed.

    Since your post showed that you were retrieving the value of @WeatherFieldName from a table, I fell into the trap of trusting the value.

    @Thom is correct.  The use of QUOTENAME will render both @WeatherFieldName and @TemperatureFieldName safe

    SET @Query = 'INSERT INTO #FieldValues ( ' + QUOTENAME(@WeatherFieldName) + ', ' + QUOTENAME(@TemperatureFieldName) + ' )
    VALUES ( @WeatherValue, @TemperatureValue )';

    EXEC sys.sp_executesql @stmt = @Query
    , @params = N'@WeatherValue varchar(500), @TemperatureValue varchar(500)'
    , @WeatherValue = @inParm_WeatherValue
    , @TemperatureValue = @inParm_TemperatureValue;

Viewing 15 posts - 1 through 15 (of 15 total)

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