Select all rows if @Parameter = NULL

user_6043544 0
Viewed 36

I have a stored procedure with a parameter, @PolicySymbol, which can accept multiple values. For that I am using a split string function: StringOfStringsToTable

If user selects @ControlNo, then I want to bring data for that controlNo no matter what value is set for @PolicySymbol.

If @PolicySymbol = NULL I want to bring all data for controlNo no matter what policy symbol it related to.

Here is the code sample:

CREATE TABLE #Test (ControlNo int, policy_symbol varchar(50))

INSERT INTO #Test 
VALUES (1111, 'CSE'), (2222, 'PRE'), (3333, 'CSE'), (4444, 'GLG'),
       (4444, 'PRE'), (4444, 'GLS') 

DECLARE 
    @ControlNo int = 1111,
    @PolicySymbol varchar(50) = NULL 

SELECT DISTINCT ControlNo, policy_symbol
FROM #Test
WHERE ControlNo = COALESCE(@ControlNo, ControlNo)
 -- here, if parameter @PolicySymbol IS NULL I want to include all policy symbols
  AND policy_symbol IN (SELECT String FROM [dbo].[StringOfStringsToTable] (@PolicySymbol, ','))

DROP TABLE #Test
sql-server-2017 stored-procedures tsql sql-server
1 Answers

A simple, logical OR does the trick.

WHERE ControlNo = COALESCE(@ControlNo, ControlNo)
-- If parameter @PolicySymbol IS NULL I want to include all policy symbols
AND (
    @PolicySymbol IS NULL
    OR policy_symbol IN (SELECT String FROM [dbo].[StringOfStringsToTable] (@PolicySymbol,','))
)
user_11274280
Related