How do I add the datepart (year, incidentdate) within the sql dynamic string

Question

Post reply

How do I add the datepart (year, incidentdate) within the sql dynamic string

Ajdba

Ten Centuries

Points: 1051
More actions
July 23, 2012 at 11:43 pm

#259780

Hi,
I have the following 2 queries as follows-
1st part-
SELECT @sql_str = N'SELECT IncidentIdNbr, incident_year, incident_date, incident_time ,county_desc, type_desc,a.county, a.rt, b.rt, b. rtnum
from VW_RPT_DATA a
INNER JOIN VW_HIST_DATA b on a.RT = b.Rt
WHERE b.rtnum = ''' + @RNum + '''
and a.county= ''' + @County + ''''
2nd part-
SELECT @sql_str = @sql_str + ' and (DATEPART(YEAR,a.incident_date) = '''+ @Year + ''''
Basically, I am trying to add 2nd part of the sql string with the 1st part of the string; I am passing @year as a parameter and I need to get the year part from the incident_date column which is datetime column but converted into varchar in order to get just the date as '01-01-2011' ; appreciate the help.
Thanks

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

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

mr.zl8888 SSC Eights! Points: 815 More actions · Answer 1

Don't know whether is this you looking for. You can always use print to print out the query to check.

DECLARE @sql_str nvarchar(max)

DECLARE @RNum varchar(max)

DECLARE @County varchar(max)

DECLARE @Year varchar(max)

SELECT @sql_str = N'SELECT IncidentIdNbr, incident_year, incident_date, incident_time ,county_desc, type_desc,a.county, a.rt, b.rt, b. rtnum

from VW_RPT_DATA a

INNER JOIN VW_HIST_DATA b on a.RT = b.Rt

WHERE b.rtnum = ' + ISNULL(@RNum,'') + '

and a.county= ' + ISNULL(@County,'')

SELECT @sql_str = @sql_str + ' and DATEPART(YEAR,a.incident_date) = '+ ISNULL(@Year,'')

PRINT @sql_str

Ajdba Ten Centuries Points: 1051 More actions · Answer 2

Thanks so much, but when I am running the query Datepart function is not getting the year from the column which should be 2011

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 3

Ajdba (7/24/2012)
Thanks so much, but when I am running the query Datepart function is not getting the year from the column which should be 2011

What is the datatype of the column "incident_date" in the VW_RPT_DATA View( I hope this is a View ), Is it VARCHAR or DATETIME?

If it is VARCHAR of format dd-mm-yyyy as you have described, you can use the RIGHT function to get the the year part instead of DATEPART

Your second statement will then become as below

SELECT @sql_str = @sql_str + ' and RIGHT(a.incident_date,4) = '+ ISNULL(@Year,'')

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Ajdba Ten Centuries Points: 1051 More actions · Answer 4

Right function is not getting the data; in the table it is datetime but I converted to varchar(10) in the view like this...

CONVERT(VARCHAR(10),I.DateOfIncidentDte, 110 ) as incident_date

Thanks

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

There are a couple of issue with this code. First is nonSARGable. You are doing date conversion functions in your where clause so you will be stuck with index scans.

Something like this would solve that issue:

declare @Year int = 2012 --Assuming your original parameter is an in

declare @YearStart datetime = '1/1/' + cast(@Year as char(4))

declare @YearEnd datetime = dateadd(yy, datediff(yy, 0, '1/1/' + cast(@Year as char(4))) + 1, 0)

select @YearStart, @YearEnd

Then your date check could become

and a.incident_date >= @YearStart and a.incident_date < @YearEnd

Now at least you can get index seeks on your incident_date index.

The bigger concern is that this appears to be wide open to sql injection. It looks like you are building a dynamic sql string and then executing it. That means it is totally vulnerable. You can execute dynamic strings like this with parameters to prevent sql injection. Let me know if you need some help with that.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Ajdba Ten Centuries Points: 1051 More actions · Answer 6

Yes, you are right; basically, I am getting the @year as varchar(4) parameter and I need to add that in the dynamic sql where clause with other condition. Can u please explain how I can be able to do sql injection

Thanks

Ajdba Ten Centuries Points: 1051 More actions · Answer 7

Sorry, I meant to say I need to know how can prevent from sql injection

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Ajdba (7/24/2012)
Sorry, I meant to say I need to know how can prevent from sql injection

Best way is not to use dynamic SQL. Are you sure you need to?

SELECT

IncidentIdNbr, incident_year, incident_date, incident_time ,county_desc,

type_desc,a.county, a.rt, b.rt, b. rtnum

from VW_RPT_DATA a

INNER JOIN VW_HIST_DATA b on a.RT = b.Rt

WHERE b.rtnum = @RNum

and a.county = @County

DATEPART(YEAR,a.incident_date) = @Year

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Ajdba Ten Centuries Points: 1051 More actions · Answer 9

Yes, I need to ; because I am modifying the existing procedure where it written in dynamic sql

Sean Lange SSC Guru Points: 286590 More actions · Answer 10

Ajdba (7/24/2012)
Sorry, I meant to say I need to know how can prevent from sql injection

I agree 100% with Chris. The easiest way to prevent sql injection in this is to not use dynamic sql. Of course this may be stripped down a bit for posting so if you really MUST use dynamic sql you can do it like this.

SELECT

IncidentIdNbr, incident_year, incident_date, incident_time ,county_desc,

type_desc,a.county, a.rt, b.rt, b. rtnum

from VW_RPT_DATA a

INNER JOIN VW_HIST_DATA b on a.RT = b.Rt

WHERE b.rtnum = @_RNum

and a.county = @_County

and a.incident_date >= @_YearStart and a.incident_date < @_YearEnd

EXEC sp_executesql @sSQL, N'@_RNum int, @_County int, @_YearStart datetime, @_YearEnd datetime',

@_RNum = @RNum, @County = @_County, @_YearStart = @YearStart, @_YearEnd = @YearEnd

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

Ajdba (7/24/2012)
Yes, I need to ; because I am modifying the existing procedure where it written in dynamic sql

Just because it was written that way originally does not mean it has to stay that way. If you see something wrong you should do your best to make it better. 🙂 From what you posted there is absolutely no reason this needs to be dynamic sql.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

demonfox SSCertifiable Points: 6289 More actions · Answer 12

Although working in SSIS, i have been a fan of dynamic sql; it is preferred to avoid dynamic sql's.

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

avishwithu (7/24/2012)
I tried the run the query that you have posted ..
these are the steps :
CREATE TABLE VW_RPT_DATA (county varchar(100), rt varchar(100),incident_date datetime)
INSERT INTO VW_RPT_DATA values('a','b',GETDATE()),('a','b',GETDATE()-400)
CREATE TABLE VW_HIST_DATA (Rt varchar(100),rtnum varchar(100))
INSERT INTO VW_HIST_DATA VALUES ('b','rtnum'),('check','rtnum2')
DECLARE @rnum varchar(100)='rtnum'
DECLARE @county varchar(100)='a'
DECLARE @sql_str varchar(1000)
DECLARE @year varchar(10)='2012'
SELECT @sql_str = N'SELECT a.incident_date,a.county, a.rt, b.rt, b. rtnum
FROM VW_RPT_DATA a
INNER JOIN VW_HIST_DATA b on a.RT = b.Rt
WHERE b.rtnum = ''' + @RNum + '''
AND a.county= ''' + @County + ''''
SELECT @sql_str = @sql_str + ' and DATEPART(YEAR,a.incident_date) = '''+ @Year + ''''
--and then execute the dynamic sql
exec (@sql_str )
It does filters for the year and gives the result as per the year filter.

And the following works just as well without the dynamic sql, and will actually use an index that is declared on incident_date.

CREATE TABLE VW_RPT_DATA (county varchar(100), rt varchar(100),incident_date datetime)

INSERT INTO VW_RPT_DATA values('a','b',GETDATE()),('a','b',GETDATE()-400)

CREATE TABLE VW_HIST_DATA (Rt varchar(100),rtnum varchar(100))

INSERT INTO VW_HIST_DATA VALUES ('b','rtnum'),('check','rtnum2')

DECLARE @rnum varchar(100)='rtnum';

DECLARE @county varchar(100) = 'a';

DECLARE @year varchar(10) = '2012';

SELECT

a.incident_date,

a.county,

a.rt,

b.rt,

b.rtnum

FROM

VW_RPT_DATA a

INNER JOIN VW_HIST_DATA b

on a.RT = b.Rt

WHERE

b.rtnum = @RNum

AND a.county = @County

AND a.incident_date >= dateadd(yy, @Year - 1900, 0)

AND a.incident_date < dateadd(yy, @Year - 1900 + 1, 0);

go

drop table VW_RPT_DATA;

drop table VW_HIST_DATA;

go

demonfox SSCertifiable Points: 6289 More actions · Answer 14

Thanks Lynn.

I was just curious, how could it not work ..

Avinash

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm: