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

gfrancoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
ALTER PROCEDURE [dbo].[Get_Patients]
      
      @PatientName varchar(50) = NULL,
      @PatientLastName varchar(50) = NULL,
      @PatientGender varchar(1)= NULL ,
      @PatientDOB date = NULL
      
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  is null or PatientName                  = @PatientName) and
                  (@PatientLastName is null or PatientLastName            = @PatientLastName) and
                  (@PatientGender is null or PatientGender            = @PatientGender ) and
                  (@PatientDOB is null or PatientDOB                  = @PatientDOB )
      ORDER BY PatientID      
END
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Brian CroweDatabase AdministratorCommented:
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)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.