user1640256 user1640256 - 5 months ago 8
SQL Question

SQL search using condition in case statement

I need to perform a search based on condition. If I pass @SearchCondition = 1 then the search should be performed on OrderId column for the passed @SearchValue If I pass @SearchCondition = 0 the search must return all the records minus the record with @SearchValue

IF @SearchCondition = 1
Search Records With [OrderId] = @SearchValue

IF @SearchCondition = 0
Get All The Records With [OrderId] <> @SearchValue


But the above needs to be done in a single statement (ideally
CASE
statement). No
IF-ELSE
since the query is too big and I do not want to write the same long query twice but with only a single different line. Here is the sample table for convenience:

DECLARE @SearchCondition INT = 0, @SearchValue INT = 121
CREATE TABLE #TempData
(
[Id] INT IDENTITY(1,1),
[OrderId] INT,
[OrderName] VARCHAR(20)
)
INSERT INTO #TempData
SELECT 121, 'A1' UNION
SELECT 122, 'A2' UNION
SELECT 123, 'A3' UNION
SELECT 124, 'A4' UNION
SELECT 125, 'A5' UNION
SELECT 126, 'A6' UNION
SELECT 127, 'A7' UNION
SELECT 128, 'A8' UNION
SELECT 129, 'A9' UNION
SELECT 130, 'A10'

--If SearchCondition = 1
SELECT [Id], [OrderId], [OrderName]
FROM #TempData
WHERE [OrderId] = @SearchValue--If SearchCondition = 1
-- Handle If SearchCondition = 0
--SELECT [Id], [OrderId], [OrderName]
--FROM #TempData
--WHERE [OrderId] <> @SearchValue

DROP TABLE #TempData


Need to merge the above statements in a single statement

Answer

This is called a Catch-all Query. One way is to use AND-OR combinations

SELECT [Id], [OrderId], [OrderName] 
FROM #TempData
WHERE 
    (@SearchCondition = 1 AND [OrderId] = @SearchValue)
    OR (@SearchCondition = 0 AND [OrderId] <> @SearchValue)

Additional Reading:

Comments