Tuesday, June 26, 2007

[SQL] Complex WHERE Condition


WHERE [PoliceDeptId] IN
(
SELECT
CASE
WHEN @PoliceDeptId IS NULL THEN [Id]
ELSE @PoliceDeptId
END
FROM [Service.CPIC.PoliceDept]
)

-- @FileType
AND [FileType] IN
(
SELECT
CASE
WHEN @FileType IS NULL THEN [FileType]
ELSE @FileType
END
FROM
(
-- Awaiting Check
SELECT [FileType] = 1
UNION
-- Awaiting Confirmation
SELECT [FileType] = 2
) AS [FileTypes]
)

-- @FirstName
AND [FirstName] IN
(
SELECT
CASE
WHEN @FirstName IS NULL THEN [FirstName]
ELSE @FirstName
END
FROM [dbo].[Candidate]
)

-- @LastName
AND [LastName] IN
(
SELECT
CASE
WHEN @LastName IS NULL THEN [LastName]
ELSE @LastName
END
FROM [dbo].[Candidate]
)


No comments: