@AccountNumber As Varchar(50),
@FirstName As Varchar(50),
@LastName As Varchar(50),
@ActiveClinicID As Varchar(50),
@ActivePhysicianID As Varchar(50),
@ActivePON As Varchar(50),
@TaxNumber As Varchar(50),
@DateOfBirth As Datetime
AS
BEGIN
Declare @Where AS Varchar(2000)
declare @v_Query as varchar(3000)
Set @Where='Where 1=1'
IF @AccountNumber <> ''
BEGIN
SET @Where =@Where + ' AND PatientMaster.AccountNumber = '''+@AccountNumber+''''
END
IF @FirstName <> ''
BEGIN
SET @Where =@Where + ' AND PatientMaster.FirstName LIKE ''%'+ @FirstName+'%'''
END
IF @LastName <> ''
BEGIN
SET @Where =@Where + ' AND PatientMaster.LastName LIKE ''%'+ @LastName+'%'''
END
IF @ActiveClinicID <> ''
BEGIN
SET @Where =@Where + ' AND PatientMaster.ActiveClinicID ='''+ @ActiveClinicID+''''
END
IF @ActivePhysicianID <> ''
BEGIN
SET @Where =@Where + ' AND PatientMaster.ActivePhysicianID ='''+ @ActivePhysicianID+''''
END
IF @ActivePON <> ''
BEGIN
SET @Where =@Where + ' AND PatientMaster.ActivePON ='''+ @ActivePON+''''
END
IF @DateOfBirth <> ''
BEGIN
--SET @Where =@Where + ' AND PatientMaster.DateOfBirth='''+ CONVERt(CHAR(10),@DateOfBirth,101)+''''
SET @Where =@Where + ' AND CAST (CONVERt(CHAR(50),PatientMaster.DateOfBirth,101)AS DATETIME)='''+ CONVERt(CHAR(10),@DateOfBirth,101)+''''
END
set @v_Query='SELECT PatientMaster.id, PatientMaster.Fullname, PatientMaster.DateOfBirth, PatientMaster.ActivePON,(staffMaster.FirstName +'' ''+staffMaster.LastName) As Physician, clinicMaster.Abbreviation as Clinic
FROM PatientMaster INNER JOIN
clinicMaster ON PatientMaster.ActiveClinicID = clinicMaster.clinicid INNER JOIN
staffMaster ON PatientMaster.ActivePhysicianID = staffMaster.staffid ' + @Where
exec(@v_Query)