Имеются три таблицы различной структуры: Persons, Legals, Naturals,
в каждой из трех таблиц поле PersonID - первичный ключ,
значения данного поля в Legals и Naturals являются подмножествами значений из Persons.
Имеются связи:
Primary key Persons.PersonID - Foreign Key Legals.PersonID
Primary key Persons.PersonID - Foreign Key Naturals.PersonID

ХП должна вернуть записи, в которых значения определенных полей совпадают
с значениями параметров. С помощью нижеприведенного запроса вроде бы получил
желаемый результат.
Вопрос - можно ли то же самое получить без использования UNION ?

CREATE PROCEDURE MyProc
@PersonID int=NULL,
@PersonType nvarchar(1)=NULL,
@PersonName nvarchar(250)=NULL,
@Inn nvarchar(12)=NULL,
@NameFull nvarchar(250)=NULL,
@NameShort nvarchar(50)=NULL,
@Family nvarchar(100)=NULL,
@FirstName nvarchar(25)=NULL,
@LastName nvarchar(25)=NULL

AS

SELECT ps.PersonID AS PersonID,
ps.PersonType AS PersonType,
ps.PersonName AS PersonName,
ps.Inn AS Inn,
lp.NameFull AS NameFull,
lp.NameShort AS NameShort,
NULL AS Family,
NULL AS FirstName,
NULL AS LastName
FROM Persons AS ps INNER JOIN Legals AS lp ON ps.PersonID=lp.PersonID
WHERE RTRIM(LTRIM(ps.PersonType))='Ю'
AND ( (ps.PersonName IS NOT NULL AND RTRIM(LTRIM(ps.PersonName))=RTRIM(LTRIM(@PersonName)))
OR (ps.Inn IS NOT NULL AND RTRIM(LTRIM(ps.Inn)) like RTRIM(LTRIM(@Inn)))
OR (lp.NameFull IS NOT NULL AND RTRIM(LTRIM(lp.NameFull))=RTRIM(LTRIM(@NameFull)))
OR (lp.NameShort IS NOT NULL AND RTRIM(LTRIM(lp.NameShort))=RTRIM(LTRIM(@NameShort)))
)
UNION
SELECT ps.PersonID AS PersonID,
ps.PersonType AS PersonType,
ps.PersonName AS PersonName,
ps.Inn AS Inn,
NULL AS NameFull,
NULL AS NameShort,
np.Family AS Family,
np.FirstName AS FirstName,
np.LastName AS LastName
FROM Persons AS ps INNER JOIN Naturals AS np ON ps.PersonID=np.PersonID
WHERE RTRIM(LTRIM(ps.PersonType))='Ф'
AND ( (ps.PersonName IS NOT NULL AND RTRIM(LTRIM(ps.PersonName))=RTRIM(LTRIM(@PersonName)))
OR (ps.Inn IS NOT NULL AND RTRIM(LTRIM(ps.Inn)) like RTRIM(LTRIM(@Inn)))
OR (np.Family IS NOT NULL AND RTRIM(LTRIM(np.Family))=RTRIM(LTRIM(@Family))
AND np.FirstName IS NOT NULL AND RTRIM(LTRIM(np.FirstName))=RTRIM(LTRIM(@FirstName))
AND np.LastName IS NOT NULL AND RTRIM(LTRIM(np.LastName))=RTRIM(LTRIM(@LastName))
)
)
UNION
SELECT ps.PersonID AS PersonID,
ps.PersonType AS PersonType,
ps.PersonName AS PersonName,
ps.Inn AS Inn,
NULL AS NameFull,
NULL AS NameShort,
NULL AS Family,
NULL AS FirstName,
NULL AS LastName
FROM Persons AS ps
WHERE (RTRIM(LTRIM(ps.PersonType))='А' OR ps.PersonType IS NULL)
AND ( (ps.PersonName IS NOT NULL AND RTRIM(LTRIM(ps.PersonName))=RTRIM(LTRIM(@PersonName)))
OR (ps.Inn IS NOT NULL AND RTRIM(LTRIM(ps.Inn)) like RTRIM(LTRIM(@Inn)))
)