Create procedure [dbo].[SearchPersonDetails]
@FirstName varchar(50),
@LastName varchar(50),
@HouseName varchar(30),
@HouseNumber varchar(30),
@City varchar(30),
@PO varchar(30),
@Pin varchar(10),
@State varchar(30)
as
begin
SELECT
Person_Id,
FirstName,
LastName,
Person_Address.value('(/person//HouseName/node())[1]', 'nvarchar(max)') as HouseName,
Person_Address.value('(/person//HouseNumber/node())[1]', 'nvarchar(max)') as HouseNumber,
Person_Address.value('(/person//City/node())[1]', 'nvarchar(max)') as City,
Person_Address.value('(/person//PO/node())[1]', 'nvarchar(max)') as PO,
Person_Address.value('(/person//Pin/node())[1]', 'nvarchar(max)') as Pin,
Person_Address.value('(/person//State/node())[1]', 'nvarchar(max)') as Person_State from tblPerson
where FirstName like '%'+@FirstName+'%' and LastName like '%'+@LastName+'%' and
Person_Address.value('(/person//HouseName)[1]', 'nvarchar(max)') like '%'+@HouseName+'%' and
Person_Address.value('(/person//HouseNumber)[1]', 'nvarchar(max)') like '%'+@HouseNumber+'%' and
Person_Address.value('(/person//City)[1]', 'nvarchar(max)') like '%'+@City+'%' and
Person_Address.value('(/person//PO)[1]', 'nvarchar(max)') like '%'+@PO+'%' and
Person_Address.value('(/person//Pin)[1]', 'nvarchar(max)') like '%'+@Pin+'%' and
Person_Address.value('(/person//State)[1]', 'nvarchar(max)') like '%'+@State+'%'
end