adding current date as a parameter to a query block

Question

I have a query block for retrieving data from MSSQL Server. the query has some hardcoded date values which needs to be changed everyday to import the daily feed. I need to automate this execution. I am using cloverETL for executing the query right now. Here is the query (its a query to retrieve sharepoint activity data) use

DocAve_AuditDB;
DECLARE
@ParameterValue VARCHAR(100),
@SQL
VARCHAR(MAX)
SET
@SQL = STUFF((SELECT 'UNION ALL SELECT COL_ItemTypeName, COL_UserName, COL_MachineIp, COL_DocLocation, DATEADD(SECOND, COL_Occurred / 1000, ''19700101 00:00'') as Date_Occurred, COL_EventAction FROM '+ TABLE_NAME + ' WHERE DATEADD(SECOND, COL_Occurred / 1000, ''19700101 00:00'') BETWEEN '+ '''20120515'''+ 'AND' + '''20120516'''+ 'AND ' + 'COL_ItemTypeName='+ '''Document''' AS 'data()'
FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_NAME LIKE '%2012_05%'
FOR
XML PATH('')),1,10,'')
EXEC
(@SQL)

In the above block I want the TABLE_NAME LIKE param i.e. %2012_05% to be a variable retrieved from the current data and also the date values in the between clause

BETWEEN '+ '''20120515'''+ 'AND' + '''20120516''' to be todays date-1 and todays date

should create a small java program for handling this or it can be done directly in the query itself? if yes how?

Thanks in Advance

Answer 1

Dynamic query is vulnerable to SQL Injection attacks depending upon the usage and environment. As far as the possibility in TSQL is concerned, this is how you can do it

DECLARE
@ParameterValue VARCHAR(100),
@SQL VARCHAR(MAX),
@TodaysDateInYYYYMMDD_Format VARCHAR(10),
@LastDayInYYYYMMDD_Format VARCHAR(10),
@TodaysDateInYYYY_MM_DD_Format VARCHAR(10)

SELECT @TodaysDateInYYYYMMDD_Format = CONVERT(VARCHAR, CURRENT_TIMESTAMP, 112)
     , @LastDayInYYYYMMDD_Format = CONVERT(VARCHAR, CURRENT_TIMESTAMP-1, 112)
     , @TodaysDateInYYYY_MM_DD_Format = REPLACE(CONVERT(VARCHAR, CURRENT_TIMESTAMP, 102), '.', '_')

SELECT @TodaysDateInYYYYMMDD_Format, @LastDayInYYYYMMDD_Format, @TodaysDateInYYYY_MM_DD_Format


SET
@SQL = STUFF((SELECT 'UNION ALL SELECT COL_ItemTypeName, COL_UserName, COL_MachineIp, COL_DocLocation, DATEADD(SECOND, COL_Occurred / 1000, ''19700101 00:00'') as Date_Occurred, COL_EventAction FROM '+ TABLE_NAME + ' WHERE DATEADD(SECOND, COL_Occurred / 1000, ''19700101 00:00'') BETWEEN '+ '''' + @LastDayInYYYYMMDD_Format + '''' + 'AND' + '''' + @TodaysDateInYYYYMMDD_Format + '''' + 'AND ' + 'COL_ItemTypeName='+ '''Document''' AS 'data()'
FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_NAME LIKE '%' + @TodaysDateInYYYY_MM_DD_FoFORt + '%'
FOR
XML PATH('')),1,10,'')
PRINT @SQL
--EXEC
--(@SQL)

adding current date as a parameter to a query block

Follow this question

Related Questions