Return date which I get as parameter in Stored Procedure

Question

This is my stored procedure which I am connecting with crystal report and I want to return start date and end date which is I am getting from parameters like if date is null then nothing shows on reports but if date has some value then that value prints.

CREATE PROCEDURE [dbo].PatientClaimInfo

@StartDate Date = NULL,
@EndDate Date = NULL

AS
BEGIN
select p.VLNAME + ' ' + p.VFNAME AS Patients_Name, p.IPATID AS Patient_ID, p.DDOB AS dob,
       d.NCOPAY, d.NVTOTPLAN, d.NVWOPLAN, d.NVWOPAT, d.NVADJPLAN, d.NVADJPAT, d.NVPAIDPLAN,
       d.NVPAIDPAT, d.NVBALPLAN, d.NVBALPAT, d.NAPPTBAL, d.VPAYSTAT AS Status
From   pmvixtr d

INNER JOIN  pmptxft p ON p.IPATID = d.IPATID


Where @StartDate <= d.DSDATE AND @EndDate >= d.DSDATE

END

Answer 1

In your select statement you can include @StartDate, @EndDate

e.g.

select @StartDate, @EndDate, .... <rest of your select statement>...

I would also suggest in your where clause use Where d.DSDATE BETWEEN(@StartDate, @EndDate)

If you don't want to select anything if @StartDate and @EndDate is NULL, having that in WHERE Clause can be very expensive... I suggest have an if condition

IF @StartDate IS NOT NULL AND @EndDate IS NOT NULL
select.....

END IF