Venkat S

Venkat S

  • 1.5k
  • 234
  • 203.1k

Searching query using fromdate and todate

Dec 23 2011 12:55 AM
Hai Friends,

i have requirement like serching (state,district,mandal,crops,username,and fromdate ,todate) wise..

i write query like this its working upto usernames but i selct fromdate and todate its not working whats wrong this ..

plz help me....

My query is

ALTER Proc [dbo].[sp_SearchAllReports1]
-- sp_SearchAllReports1  null,null,null,null,null,'10/11/2001'

 @intStateId int =null,
 @intDistrictId int=null,
 @intMandalId int=null,
 @CropsList varchar(50) =null,
 @varCreatedBy varchar(50)=null,
 @fromdate date=null,
 @todate date=null 

as 
Begin 
  declare @query varchar (max)
  set @query='SELECT tabProgram.varProgramCode, dbo.tabDistricts.varDistrictName, dbo.tabstates.varstatename,  dbo.tabMandal.varMandalName,dbo.tabProgram.varVillageName, dbo.tabProgram.intStateId, dbo.tabProgram.intDistrictId, dbo.tabProgram.intFarmers, dbo.tabProgram.intMandalId,dbo.tabProgram.ProgSponseredBy, dbo.tabProgram.DateOfConducting,
  dbo.tabProgram.intProgramId AS Expr1,
  dbo.tabProgram.CropsList, tabProgram.varVillageName ,
  tabMandal.varMandalName, 
  tabDistricts.varDistrictName
  ,CONVERT(varchar(20),casestudy.dtConducteddate,103)as dtConducteddate,
  dbo.casestudy.* FROM dbo.tabProgram
  INNER JOIN dbo.tabMandal
  ON
  dbo.tabProgram.intMandalId = dbo.tabMandal.intMandalId INNER JOIN dbo.tabDistricts ON dbo.tabProgram.intDistrictId = dbo.tabDistricts.intDistrictId INNER JOIN dbo.tabstates ON dbo.tabDistricts.intstateid = dbo.tabstates.intstateid INNER JOIN dbo.casestudy ON dbo.tabProgram.intProgramId = dbo.casestudy.intProgramId  where 1=1' 
    if (@intStateId <> '0')
    begin
    set @query=@query+ ' and tabProgram.intStateId ='+convert (varchar(10),@intStateId)
    end 
    if (@intDistrictId <> '0')
    begin
    set @query=@query+ ' and tabProgram.intDistrictId ='+convert (varchar(10),@intDistrictId)
    end
    if (@intMandalId <> '0')
    begin
    set @query=@query+ ' and tabProgram.intMandalId ='+convert (varchar(10),@intMandalId)
    end 
    if @CropsList is not null
    begin
    set @query=@query+ ' and tabProgram.CropsList like'+ '%'+@CropsList+'%'
    end 
    if @varCreatedBy is not null
    begin
    set @query=@query+ ' and tabProgram.varCreatedBy ='+@varCreatedBy
    end
    if @fromdate is not null
    begin
    set @query=@query+ 'and  CAST(casestudy.dtConducteddate AS DATE)  < '+ Convert (varchar(10),CAST(@fromdate AS DATE))
    end 
    if @todate is not null
    begin
    set @query=@query+ ' and  CAST(casestudy.dtConducteddate AS DATE)  < '+ Convert (varchar(10),CAST(@todate AS DATE))
    end


   
    exec (@query)
     
end


i hope somebody help...
Thank
Venkat.S

Answers (4)