Click here to Skip to main content
15,905,414 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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

SQL
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
Posted
Updated 22-Dec-11 19:00pm
v2
Comments
patil.ravi035 23-Dec-11 1:37am    
Let us know what error your getting?

First of all, I don't know why you have Begin-Set-End for the various where conditions. All can be directly written as it's a stored procedure where all the query parameters are already present in a form that can be used directly.
Null checks if any can be done earlier in business/database layer based on need. Even if in the SP then it can be done in one shot at the start.

Now, for DATE comparison, you can use BETWEEN keyword also. Have a look at the following:
BETWEEN (Transact-SQL)[^]

Thus, your where clause should be something like:
SQL
SELECT * FROM MyTable WHERE DBCheckDate BETWEEN @FromDate AND @ToDate

--OR 

SELECT * FROM MyTable WHERE DBCheckDate > @FromDate AND DBCheckDate < @ToDate 
 
Share this answer
 
Just try it..

First convert the date using 101 date format

This is your query

set @query=@query+ ' and  CAST(casestudy.dtConducteddate AS DATE)  < '+ Convert (varchar(10),CAST(@todate AS DATE))


Try this

set @query=@query+ ' and  CAST(casestudy.dtConducteddate AS DATE)  < '+ Convert(varchar(10),@todate,101)

I hope this will work.
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900