dynamic sql

• thaya2002

  Old Hand

  Points: 393

  More actions

  March 18, 2008 at 4:13 pm

  #183510

  create Procedure FortnightlyJobTransaction

  @@FilterTablevarchar(50)= null,

  @@FortnightEndDate datetime =null,

  @@StartEmployeeNo varchar(50) =null,

  @@EndEmployeeNo varchar(50)=null

  as

  set dateformat dmy

  --set @@FortnightEndDate ='2003/06/29'

  declare

  @sql varchar(8000),

  @FEndDate varchar(12),

  @FNEndDate Datetime

  set @FEndDate=(select Convert(varchar(10),@@FortnightEndDate,103))

  set @FNEndDate=(select Convert(datetime,@FEndDate))

  set @@FortnightEndDate=@FNEndDate

  -- Create Temporary Table

  create table #tmp0 (

  JobTranID int,

  JobCode varchar(20),

  TranType varchar(1),

  Trandate datetime,

  HierarchyCode1 varchar(20),

  TimeCode varchar(5),

  Quantity numeric(16,2),

  PersonID int,

  EmployeeNo varchar(50),

  Surname varchar(50),

  FirstName varchar(50)

  )

  -- Populate Temporary Table according to filter criteria

  set @sql = 'insert into #tmp0 (JobTranID,JobCode,TranType,TranDate)

  select t.JobTranID,t.JobCode,t.TranType,t.TranDate from JobTransactions t,' + @@FilterTable +

  ' where t.JobCode = ' + @@FilterTable +

  '.JobCode ' +

  'and'+ '('+ 't.trantype=''T'''+ 'or t.trantype=''D'''+')'

  'and(t.trandate>='+@@FortnightEndDate-13+'and t.trandate=' + @@FortnightEndDate +')'

  exec (@SQL)

  ....

  ....

  ...

  when i try to execute the above dynamic sql, it gives me convertion error.

  'and(t.trandate>='+@@FortnightEndDate-13+'and t.trandate=' + @@FortnightEndDate +')'--only problem in this line.

  i want to populate the data into #tmp0 using filter by date.

  cant i define the @@FortnightEndDate as datetime and then compare that with the trandate(which is the date field in the table) inside the dynamic sql.

  should i define that parameter as a varchar.

  regards,

  ts

• Jeff Moden

  SSC Guru

  Points: 1004363

  More actions

  March 18, 2008 at 4:24 pm

  #791194

  If you print your SQL variable, you'll find that your @@FortnightEndDate variables are returning values that need quotes around them... it's an easy fix...

  'and(t.trandate>='''+@@FortnightEndDate-13+'''and t.trandate=''' + @@FortnightEndDate +''')'

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  March 18, 2008 at 7:24 pm

  #791232

  I think that you might also be missing a plus sign ("+") between these two lines:

  'and'+ '('+ 't.trantype=''T'''+ 'or t.trantype=''D'''+')'

  'and(t.trandate>='+@@FortnightEndDate-13+'and t.trandate=' + @@FortnightEndDate +')'

  [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
  Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

• antonio.collins

  SSCrazy Eights

  Points: 9600

  More actions

  March 18, 2008 at 7:26 pm

  #791233

  thaya2002 (3/18/2008)

  ---

  create Procedure FortnightlyJobTransaction

  @@FilterTablevarchar(50)= null,

  @@FortnightEndDate datetime =null,

  @@StartEmployeeNo varchar(50) =null,

  @@EndEmployeeNo varchar(50)=null

  as

  ...

  -- Create Temporary Table

  create table #tmp0 (

  ... )

  -- Populate Temporary Table according to filter criteria

  set @sql = 'insert into #tmp0 (JobTranID,JobCode,TranType,TranDate)

  select t.JobTranID,t.JobCode,t.TranType,t.TranDate from JobTransactions t,' + @@FilterTable +

  ' where t.JobCode = ' + @@FilterTable +

  '.JobCode ' +

  'and'+ '('+ 't.trantype=''T'''+ 'or t.trantype=''D'''+')'

  'and(t.trandate>='+@@FortnightEndDate-13+'and t.trandate=' + @@FortnightEndDate +')'

  exec (@SQL)

  ...

  dynamic SQL leads to aggravating errors like the one you've encountered. because of this, i prefer to keep dynamic sql statements as simple as possible. since you're already using temp tables you shouldn't mind another and this alternative makes the coding easier.

  ...

  -- Create Temporary Table

  create table #tmp0 (

  ... )

  exec ('select distinct JobCode into #codes from '+ @@FilterTable)

  -- Populate Temporary Table according to filter criteria

  insert into #tmp0 (JobTranID,JobCode,TranType,TranDate)

  select t.JobTranID,t.JobCode,t.TranType,t.TranDate

  from JobTransactions t join #codes

  on t.JobCode = #codes.JobCode

  where

  t.trantype in ('T','D')

  and (t.trandate >= @@FortnightEndDate -13

  and t.trandate= @@FortnightEndDate)

  with this change, you don't need to worry about embedded quotes and it's plain to see that the t.trandate comparison is incorrect since it will only return data for trandate = @@FortnightEndDate. i think you want

  t.trandate between (@@FortnightEndDate - 13) and @@FortnightEndDate