need of query - 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

need of query

Author

Topic

vidhya
Posting Yak Master

108 Posts

Posted - 2008-10-09 : 01:31:37

Hi friends,

I am using table called docinfo with fields patientid, patientname,edate,status. I need to search the records by status ,fromdate,todate. For fromdate and todate i used edate field. While searching the records edate or status value may be null.

for the above requirement i need query.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-09 : 01:39:47

do you mean fields will be null or do you mean parameters can be null (optional parameters)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-09 : 01:55:42

if parameters can be null then use this

select *
from docinfo
where (edate>=@startdate or @startdate is null)
and (edate<=@enddate or @enddate is null)
and (status=@status ot @status is null)

if you want to return null fields also regardless or value passed use this

select *
from docinfo
where ((edate>=@startdate and edate<=@enddate) or edate is null)
and (status=@status ot status is null)

vidhya
Posting Yak Master

108 Posts

Posted - 2008-10-09 : 02:11:30

i need to search the records by entering edate or status or both.
IF i enter only edate field, the records need to fetch the values according to edate.
if i enter only status , the records need to fetch the values according to status.
if i enter both the edate and status, the records need to fetch the values according to edate and status.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-09 : 02:14:49

quote:
Originally posted by vidhya

i need to search the records by entering edate or status or both.
IF i enter only edate field, the records need to fetch the values according to edate.
if i enter only status , the records need to fetch the values according to status.
if i enter both the edate and status, the records need to fetch the values according to edate and status.

then use my first query

vidhya
Posting Yak Master

108 Posts

Posted - 2008-10-09 : 05:07:36

this is my query,

create procedure prosample( @startdate datetime, @enddate datetime, @status varchar(15))
as
select *
from docinfo
where (edate>=@startdate or @startdate is null)
and (edate<=@enddate or @enddate is null)
and (status=@status or @status is null) and drid=1

prosample '8/1/2008','9/1/2008',''
i execute this procedure no values displayed, its empty. but i need to diaply the records between 8/1/2008 to 9/1/2008.

prosample '8/1/2008','9/1/2008','NEW'
for this i am getting correct result.

prosample '','','NEW'
for this i am not getting the result.

I need to enter all filed values then only it displays the o/p otherwise i does not display any value.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-09 : 05:16:30

if you're passing values like earlier post change procedure like this

create procedure prosample( @startdate datetime, @enddate datetime, @status varchar(15)) 
as
select *
from docinfo
where (edate>=@startdate or @startdate='')
and (edate<=@enddate or @enddate='')
and (status=@status or @status ='') and drid=1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources