January 20, 2011 at 2:05 am
Hi,
Below one is my sample table.
Create Table t
(
Id int,
[Status] varchar(50),
StDate datetime,
)
Insert into t
Select 1,'Active',GETDATE()+5 Union
Select 2,'In-Active',GETDATE()+10 Union
Select 3,'Active',GETDATE()+14 Union
Select 4,'Active',GETDATE()+45
--Procedure
Create Procedure test (@id int,@status varchar(50),@stdt datetime,@eddt datetime)
As
Begin
Select * from t
Where (@id=0 or Id=@id) and
(@status='ALL' or [Status]=@status) and
(StDate between @stdt and @eddt or 1=1)
End
When i execute the below one
exec test 1,'All',null,null
here am getting one rows. its correct.
test 0,'All','2011-01-25 14:11:40.293','2011-02-03 14:11:40.293'
need to get Three rows
My requirement,
If pass date value am getting wrong result.
How can i change the above procedure
January 20, 2011 at 2:13 am
Of course, the results you get will depend on the date on which you execute the INSERT statement. I think you need to lose the [font="Courier New"]OR 1=1[/font], since this renders your last filter clause academic.
John
January 20, 2011 at 2:22 am
The where clause with the or 1=1 would always evaluate to true.
Change the procedure to:
alter Procedure test (@id int,@status varchar(50),@stdt datetime,@eddt datetime)
As
Begin
Select * from t
Where (@id=0 or Id=@id)
and (@status='ALL' or [Status]=@status)
and (StDate between @stdt and @eddt or (@stdt is null and @eddt is null))
End
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply