Problems with null values in Stored procedures

This should be an easy question i think, i have googled for it for a bit but not sure if i'm using the right keywords. Here it goes in this stored procedure below when the parameters are null, i want them to be ignored.I'm pretty sure there is a function that can be used to that purposed just don't really know where to get the info.

Basically if both parameters are null i want it to be a select * from patient.

Thanks ahead.

Code:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:		<Author,,Name>

-- Create date: <Create Date,,>

-- Description:	<Description,,>

-- =============================================

ALTER PROCEDURE [dbo].[SelectPatient]

	-- Add the parameters for the stored procedure here

	@RecordNumber bigint = null,

	@PatientName nchar(70) = null

AS

BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from

	-- interfering with SELECT statements.

	SET NOCOUNT ON;



    -- Insert statements for procedure here

	Select * from Patient where RecordNumber = @RecordNumber OR PatientName like '%'+@PatientName+'%'

	Print '%'+@PatientName+'%'

END

Code:

SELECT something

     , anything

     , just_not_the_dreaded_evil_select_star 

  FROM Patient 

 WHERE (

       @RecordNumber IS NULL

    OR RecordNumber = @RecordNumber 

       )

   AND (

       @PatientName IS NULL

    OR PatientName like '%'+@PatientName+'%'

       )

Problems with null values in Stored procedures

Programming

Related Pages