January 21, 2021 at 5:02 pm
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?
January 21, 2021 at 5:26 pm
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
Change is inevitable... Change for the better is not.
January 21, 2021 at 5:50 pm
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.
January 21, 2021 at 7:56 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
January 21, 2021 at 9:03 pm
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)
January 27, 2021 at 9:16 pm
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).
January 27, 2021 at 9:27 pm
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.
January 28, 2021 at 12:24 am
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) A socialist is someone who will give you the shirt off *someone else's* back.
January 28, 2021 at 1:41 am
@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?
January 28, 2021 at 7:03 am
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 );
January 28, 2021 at 2:08 pm
@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.
January 28, 2021 at 4:00 pm
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;
January 28, 2021 at 4:38 pm
@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.
January 28, 2021 at 4:42 pm
@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.
Larnu.uk
January 28, 2021 at 5:34 pm
@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-2 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 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply