Link to home
Start Free TrialLog in
Avatar of gfranco
gfrancoFlag for United States of America

asked on

SQL Server parameters

I want to use this SP with either having just 2 parameters like Patient Name, Patient LAst Namer or either Patient Name, Last Name, DOB or having the 4 all together.
Now, is working having mandatory all of 4, but the query would be having 2, 3 or 4.
What is the change that have to be done on this SP, thanks for your help.


ALTER PROCEDURE [dbo].[Get_Patients] 
	
	@PatientName varchar(50),
	@PatientLastName varchar(50),
	@PatientGender varchar(1),
	@PatientDOB date
	
AS
BEGIN
	
	SET NOCOUNT ON;

	select	PatientID as Id, PatientName AS [Name],PatientLastName as [Last Name],PatientGender as Gender ,PatientDOB as [Date of Birth]
	from	Patients
	where	
			
			PatientName			= @PatientName and
			PatientLastName		= @PatientLastName and
			PatientGender		= @PatientGender and 
			PatientDOB			= @PatientDOB 
	ORDER BY PatientID	
END

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Aneesh
Aneesh
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Default the "optional" parameters to NULL and change your where cluase to:

WHERE PatientName = ISNULL(@PatientName, PatientName)
   AND PatientLastName = ISNULL(@PatientLastName, PatientLastName)
   AND PatientGender = ISNULL(@PatientGender, PatientGender)
   AND PatientDOB = ISNULL(@PatientDOB, PatientDOB)