Proceudure in datetiem field issue

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Proceudure in datetiem field issue

Author

Topic

urzsuresh
Starting Member

30 Posts

Posted - 2011-01-20 : 04:07:12

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

Suri

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-20 : 04:15:34

or 1=1 is always true

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources