CREATE PROCEDURE [dbo].[sspTASGetPMIDetails]
(
@FirstName NVarchar(100),
@SurName NVarchar(100),
@HospitalNo NVarchar(100)
)
AS
BEGIN
SET NOCOUNT ON
/* Variable Declaration */
Declare @SQLQuery AS NVarchar(4000)
Declare @ParamDefinition AS NVarchar(2000)
/* Build the Transact-SQL String with the input parameters */
Set @SQLQuery = 'select top 1000
pat.InternalNo,
pat.CaseNoteNo,
pat.DistrictNo,
pat.SurName,
pat.Forenames,
CONVERT(VARCHAR(10),pat.DOB,105) as DOB,
pat.HomePhone,
pat.WorkPhone,
pat.Address1,
pat.Address2,
pat.Address3,
pat.Address4,
pat.PostCode,
pat.NHSNo
from pmiDetails pat'
/* check for the condition and build the WHERE clause accordingly */
If (@FirstName Is Not Null OR @SurName Is Not Null OR @HospitalNo Is Not Null)
Set @SQLQuery = @SQLQuery + ' WHERE'
If @FirstName Is Not Null
Set @SQLQuery = @SQLQuery + ' (pat.Forenames LIKE @FirstName)'
If (@SurName Is Not Null AND @FirstName Is Not Null)
Set @SQLQuery = @SQLQuery + ' And (pat.SurName LIKE @SurName)'
If (@SurName Is Not Null AND @FirstName Is Null)
Set @SQLQuery = @SQLQuery + ' (pat.SurName LIKE @SurName)'
If (@HospitalNo Is Not Null AND @FirstName Is Null AND @SurName Is Null)
Set @SQLQuery = @SQLQuery + ' (pat.CaseNoteNo LIKE @HospitalNo OR pat.DistrictNo LIKE @HospitalNo OR pat.NHSNo LIKE @HospitalNo)'
If @HospitalNo Is Not Null
Set @SQLQuery = @SQLQuery + ' AND (pat.CaseNoteNo LIKE @HospitalNo OR pat.DistrictNo LIKE @HospitalNo OR pat.NHSNo LIKE @HospitalNo)'
/* Specify Parameter Format for all input parameters included
in the stmt */
Set @ParamDefinition = ' @FirstName NVarchar(100),
@SurName NVarchar(100),
@HospitalNo NVarchar(100)'
/* Execute the Transact-SQL String with all parameter value
Using sp_executesql Command */
Execute sp_Executesql @SQLQuery,
@ParamDefinition,
@FirstName,
@SurName,
@HospitalNo
RETURN
SET NOCOUNT OFF
END
Papooze 