error in procedure - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

error in procedure

Author

Topic

vidhya
Posting Yak Master

108 Posts

Posted - 2008-12-03 : 04:21:30

create procedure [dbo].[search](@pname varchar(64),@edate varchar(18),@drid int,@appdate varchar(15))as
select * from waves
where drid =@drid and
(
(edate =@edate or patname='' or approveddate='') or
(patname like @pname or edate ='' or approveddate='')or
(patname ='' or edate ='' or approveddate=@appdate)
)

search '','',1,'8/13/2008'

If i enter value for appdate the corresponding value is not displaying.

can any one tell me what mistake i done.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-12-03 : 04:23:52

I think

approveddate=@appdate

should be

approveddate>=@appdate and approveddate<dateadd(day,1,@appdate)

Madhivanan

Failing to plan is Planning to fail

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-12-03 : 12:31:31

I'm not 100% what the logic should be and perhaps Madhivanan is correct. However, assuming your current logic is close, then you can simplify things a bit:

select * from waves
where drid =@drid and
(
	edate = @edate 
	or patname like @pname 
	or approveddate=@appdate
	or approveddate=''
	or patname ='' 
	or edate ='' 
)


-- Or even

select * from waves
where drid =@drid and
(
	edate IN (@edate, '')
	or approveddate IN (@appdate, '')
	or patname like @pname 
	or edate ='' 
)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-12-03 : 12:32:51

Also, are your "date" columns actual DATETIME data types or are they also strings?

dios
Starting Member

3 Posts

Posted - 2008-12-03 : 19:22:44

you are sending chars?, try convert
example:
(convert(char(10), txndatetime,103)=@sales_Date) and fieldX='123'
@sales_Date is a varchar(10) whit this format 'dd/mm/yyyy'

Elisamuel Xocoyotzin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources