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

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 24, 2012 at 9:48 am

  #1516909

  avishwithu (7/24/2012)

  ---

  Thanks Lynn.

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

  Avinash

  Also, I thought I'd show you another way to write your sql dynamically, not the it is needed since there is nothing requiring it in the sql code.

  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')

  GO

  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

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

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

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

  declare @SQLCmd nvarchar(max),

  @SQLParams nvarchar(max);

  set @SQLCmd =

  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

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

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

  set @SQLParams = N'@rnum varchar(100), @county varchar(100), @year varchar(10)';

  exec sp_executesql @stmt = @SQLCmd, @params = @SQLParams, @rnum = 'rtnum', @county = 'a', @Year = '2012';

  go

  drop table VW_RPT_DATA;

  drop table VW_HIST_DATA;

  go

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 24, 2012 at 9:49 am

  #1516910

  avishwithu (7/24/2012)

  ---

  Thanks Lynn.

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

  Avinash

  Sorry, not quite sure what you are asking here.

• Lokesh Vij

  SSChampion

  Points: 10836

  More actions

  July 24, 2012 at 9:57 am

  #1516919

  I will go with Sean, the best way is to un-do with dynamic SQL. If your code is not exposed and free from SQL Injections - convert your query as suggested by Sean.

  ~ Lokesh Vij
  

  ---

  Guidelines for quicker answers on T-SQL question[/url]
  Guidelines for answers on Performance questions

  Link to my Blog Post --> www.SQLPathy.com[/url]

  Follow me @Twitter

  

• demonfox

  SSCertifiable

  Points: 6289

  More actions

  July 24, 2012 at 9:57 am

  #1516920

  oh, I meant that with the initial post ,which says the dynamic sql didn't work when it actually does ..

  Avinash

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

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 24, 2012 at 10:07 am

  #1516932

  Ajdba (7/24/2012)

  ---

  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

  Keep the datetime columns as datestime columns.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 24, 2012 at 10:58 am

  #1516970

  Lynn Pettis (7/24/2012)

  ---

  Ajdba (7/24/2012)

  ---

  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

  Keep the datetime columns as datestime columns.

  To elaborate, by converting DateOfIncidentDte to a character field, you prevent any queries using this view from using an index (if it exists) on this column in the underlying table.

• demonfox

  SSCertifiable

  Points: 6289

  More actions

  July 24, 2012 at 11:14 am

  #1516977

  thanks for the explanation.

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

• Ajdba

  Ten Centuries

  Points: 1051

  More actions

  July 25, 2012 at 10:42 am

  #1517506

  Hello

  can you please look the execution plan and let me know if you see anything I can do to improve the query performance; I would appreciate it so much

  I am not too familiar with query execution plan

  Thanks

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 25, 2012 at 10:52 am

  #1517517

  Will also need the DDL for the views/tables involved including indexes defined on the tables, the code you are running.