CREATE PROCEDURE pr_PatientsSearch
        @orderby		INT         = 1,
	@d_birth 		DATETIME    = NULL,
	@s_firstname 		VARCHAR(50) = NULL,

	@s_street1 		VARCHAR(100) = NULL,
	@s_email2 		VARCHAR(100) = NULL
	
AS

SELECT 	p.u_patientid  		  	AS pu_patientid,
	ISNULL(p.s_mrno, '') 		AS ps_mrno,	
	p.d_birth			AS pd_birth,
	p.d_death 			AS pd_death,
	p.d_lastedit			AS pd_lastedit,

	a.u_addressid 			AS au_addressid, 
	a.u_entityid 			AS as_entityid, 
	ISNULL(a.s_street1, '') 	AS as_street1, 
	ISNULL(a.b_delete, 0) AS ab_delete, 
	ISNULL(a.b_default, 0) AS ab_default

FROM	t_Patient p 
		 LEFT OUTER JOIN 
			t_Address a
				ON p.u_patientid = a.u_entityid
WHERE  		(p.[b_delete] = 0) 
	AND 
		(a.[b_default]=1 OR a.[b_default]=NULL)
        AND
		(p.[d_birth] 		=    COALESCE(@d_birth, p.[d_birth]))
	AND
		(p.[s_acctno]		LIKE COALESCE(@s_acctno, p.[s_acctno]))

	AND
		(a.[s_street2] 		LIKE COALESCE(@s_street2, a.[s_street2]))
		(a.[s_city] 		LIKE COALESCE(@s_city, a.[s_city]))
	AND
		(a.[s_statecode] 	=    COALESCE(@s_statecode, a.[s_statecode]))

	AND
		(a.[s_email2] 		LIKE COALESCE(@s_email2, a.[s_email2]))
ORDER BY 
	CASE @orderby
		WHEN 1
			THEN 
			p.s_lastname+ ', ' +p.s_firstname

	END ASC,
	CASE @orderby
		WHEN 2
			THEN 
			p.s_lastname+ ', ' +p.s_firstname

	END DESC
GO