x

adding current date as a parameter to a query block

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
more ▼

asked May 31 '12 at 09:59 PM in Default

priyank15 gravatar image

priyank15
342 9 9 10

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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)
more ▼

answered Jun 01 '12 at 07:33 AM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x43

asked: May 31 '12 at 09:59 PM

Seen: 719 times

Last Updated: Jun 01 '12 at 07:33 AM