How can we use SQL column value as part of sql statement stored in another column-CodePudding

I have a scenario where I need to execute queries on different tables using ETL tool. I want to store all the required queries in control table.

As part of this, I want to include the column WatermarkValue as part of the value in the column Source_Query, so that I can dynamically use it for my execution. This is how my control table should look like.

Table Name: Metadata_Table

TableID	Source_Query	WatermarkValue
1	select * from dbo.cust_eventchanges where lastmodifieddate >{WatermarkValue}	2022-10-09T12:00:00
2	select * from dbo.cust_contacts where lastmodifieddate >{WatermarkValue}	2022-07-08T03:20:00

So when I run my metadata table like this select * from Metadata_Table where TableID=1

the result should be like below.
select * from dbo.cust_eventchanges where lastmodifieddate >'2022-10-09T12:00:00'

I know we can do this by concatenating two columns. But I would like to know if this is achievable.

I couldn't able to figure out how to achieve this. Hence, I need help on this scenario

CodePudding user response：

You can create a view and use a view in your ETL tool instead of the table.

create view vMetadataQueries 
as
  select TableID, Source_Query   WatermarkValue as [ExecuteQuery] 
  from Metadata_Table

This is not a particularly good way of doing that because it potentially leaves you open to SQL injection.

CodePudding user response：

Using sp_executesql with a typed parameter definition reduces the risk of SQL Injection

Example below shows how to run one of your queries. You could simply wrap this in a cursor where each iteration executes a different query in the metadata table.

DROP TABLE IF EXISTS #MetaData_Table
GO
CREATE TABLE #MetaData_Table
(TableID INT,Source_Query NVARCHAR(MAX),WatermarkValue DATETIME)

INSERT INTO #MetaData_Table
(TableID,Source_Query,WatermarkValue)
VALUES
(1,'select * from dbo.cust_eventchanges where lastmodifieddate >@WatermarkValue','2022-10-09T12:00:00'),
(2,'select * from dbo.cust_contacts where lastmodifieddate >@WatermarkValue','2022-07-08T03:20:00')


SELECT * FROM #MetaData_Table

DECLARE @dtVariable DATETIME;  
DECLARE @SQLString NVARCHAR(500);  
DECLARE @ParmDefinition NVARCHAR(500);  

-- You can put this in a cursor to loop through all your tables, this is hardcoded to one for simplicity.
SELECT @SQLString = Source_Query, @dtVariable = WatermarkValue FROM #MetaData_Table WHERE TableID = 1

SET @ParmDefinition = N'@WatermarkValue DATETIME'
EXECUTE sp_executesql @SQLString, @ParmDefinition,  
                      @WatermarkValue = @dtVariable;