TRY THIS
ALTER PROCEDURE [dbo].[Find_Schools]
(
@Services varchar (100),
@Schools varchar (250)
)
AS
BEGIN
DECLARE @Where VARCHAR(MAX)=''
DECLARE @SQL VARCHAR(MAX)
IF(@Services!='' AND @Schools!='')
BEGIN
SET @Where+=' and (s.SVC_NAME ='''+@Services+''') and (CI.CHLD_SchoolID = '''+@Schools+''' )'
END
ELSE IF(@Services!='' AND @Schools='')
BEGIN
SET @Where+=' and (s.SVC_NAME ='''+@Services+''')'
END
ELSE IF(@Services='' AND @Schools!='')
BEGIN
SET @Where+=' and (CI.CHLD_SchoolID = '''+@Schools+''' )'
END
SET NOCOUNT ON;
SET @SQL='
SELECT CI.CHLD_REF,CI.CHLD_ID,CI.CHLD_NAME, CI.CHLD_SURNAME, CI.CHLD_BIRTH_DATE,CI.CHLD_SchoolID, s.SVC_NAME, cs.CSVC_STATUS, cs.CSVC_UPDATE, cs.CSVC_EMPL_DN, E.Name,
E.Surname,cs.CSVCID,cs.CSVC_SVC_ID
FROM CHILD_SERVICE1 AS cs INNER JOIN
CHILD_INFO AS CI ON cs.CSVC_CHLD_ID = CI.CHLD_ID INNER JOIN
SERVICES AS s ON cs.CSVC_SVC_ID = s.SVC_ID INNER JOIN
EmpUsers AS E ON cs.CSVC_EMPL_DN = E.UserName
WHERE (cs.CSVC_REFERRED = 1 )
'+@Where+'
'
EXEC(@SQL)
END