Dynamic IN clause with condition

Question

I'm building a dynamic SQL query in which I couldn't handle a condition where the IN clause is used.

Query:

DECLARE @SQL VARCHAR(5000)

SET @SQL = 'SELECT ED.Name, ED.Age, ED.Phone, EA.Address FROM EmployeeDetails ED JOIN
            EmployeeAddress EA ON EA.EmpId = ED.EmpID WHERE
            ED.Name LIKE ''%'+@Name+'%'' AND
            EA.Status IN ('+@Status+')
            ORDER BY ED.CreatedDate DESC OFFSET ' + CAST((@PageNum * @PageSize) AS 
            NVARCHAR(10)) +' ROWS FETCH NEXT ' + CAST(@PageSize AS NVARCHAR(10)) + ' ROWS ONLY;'

EXEC sp_executesql @SQL

When I pass empty to the @Status, I get a syntax error or on the () as it isn't valid.

In the above query whenever the @Status is NULL or "", I want the condition to be ignored. Any help?

Warning, what you have is *wide* open to injection. If you want to provide a list to your query, use a table type parameter. Don't blindly inject strings into your dynamic statement either, if you "must" use dynamic SQL (and you don't here) **parametrise** your parameters. — Thom A, Apr 03 '22 at 08:08
Use an inline case expression to add the condition to the SQL statement. — Stu, Apr 03 '22 at 08:32
Does this answer your question? [SQL Server - In clause with a declared variable](https://stackoverflow.com/questions/2944511/sql-server-in-clause-with-a-declared-variable) — Thom A, Apr 03 '22 at 09:02
@Larnu This will help me to a great extent but still what If my param is empty and i wanted all the records when that happens? — A Coder, Apr 03 '22 at 09:19

score 3 · Accepted Answer · answered Apr 03 '22 at 09:39

There's no need for dynamic SQL here at all; in fact your use of it here is dangerous. You are leaving yourself wide open to SQL injection attacks. I strongly suggest you research the dangers of such a fatal vulnerability. SQLK Injection is a problem that has existed for decades and there is no excuse to writing such code any more; ignorance is not an excuse when so many companies have been named and shamed over the last 15 years for such badly written code resulting in huge numbers of data breaches.

Anyway, onto the answer. One method would be to use a table type parameter. I define a table variable here, but if this is a call from an application you'll need to look into creating and using a TVP:

SELECT ED.Name,
       ED.Age,
       ED.Phone,
       EA.Address
FROM dbo.EmployeeDetails ED
     JOIN dbo.EmployeeAddress EA ON EA.EmpId = ED.EmpID
WHERE ED.Name LIKE '%' + @Name + '%'
  AND (EA.Status IN (SELECT Status FROM @Statuses)
   OR  NOT EXISTS (SELECT 1 FROM @Statuses)
ORDER BY ED.CreatedDate DESC
OFFSET @PageNum * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY
OPTION (RECOMPILE);

Alternertively, if you are passing a delimited string for your list (which it seem you are) you could use a string splitter and split the value. This assumes you are using a fully supported version of SQL Server (if not, look into an inline user defined table value string splitter) and the value is comma (,) delimited.

SELECT ED.Name,
       ED.Age,
       ED.Phone,
       EA.Address
FROM dbo.EmployeeDetails ED
     JOIN dbo.EmployeeAddress EA ON EA.EmpId = ED.EmpID
WHERE ED.Name LIKE '%' + @Name + '%'
  AND (EA.Status IN (SELECT SS.[value] FROM STRING_SPLIT(@Status,',') SS)
   OR  @Status IS NULL)
ORDER BY ED.CreatedDate DESC
OFFSET @PageNum * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY
OPTION (RECOMPILE);

Perfect, Works well. I'll take all the suggestions given. Thanks — A Coder, Apr 03 '22 at 15:59

score -1 · Answer 2 · edited Apr 03 '22 at 08:01

Try this:

Declare @SQL NVARCHAR(5000)


SET @SQL = 'Select ED.Name, ED.Age, ED.Phone, EA.Address FROM EmployeeDetails ED JOIN
            EmployeeAddress EA ON EA.EmpId = ED.EmpID WHERE
            ED.Name LIKE ''%'+@Name+'%'' AND
            (ISNULL(@Status, '''')= '''' OR EA.Status IN (@Status))
            ORDER BY ED.CreatedDate DESC OFFSET ' + CAST((@PageNum * @PageSize) AS 
            NVARCHAR(10)) +' ROWS FETCH NEXT ' + CAST(@PageSize AS NVARCHAR(10)) + ' ROWS 
            ONLY;'

EXEC sp_executesql @SQL, N'@Status NVARCHAR(MAX)', @Status

score -1 · Answer 3 · answered Apr 03 '22 at 08:37

You can try this :

DECLARE @SQL VARCHAR(5000)

SET @SQL = 'SELECT ED.Name, ED.Age, ED.Phone, EA.Address FROM EmployeeDetails
ED JOIN
            EmployeeAddress EA ON EA.EmpId = ED.EmpID WHERE
            ED.Name LIKE ''%'+@Name+'%'' AND
            1 = Case 
            When (ISNULL('+@Status+', '''')= '''' Then 1
            Else EA.Status IN ('+@Status+')
            ORDER BY ED.CreatedDate DESC OFFSET ' + CAST((@PageNum * @PageSize) AS 
            NVARCHAR(10)) +' ROWS FETCH NEXT ' + CAST(@PageSize AS NVARCHAR(10)) + ' ROWS ONLY;'

EXEC sp_executesql @SQL

score -1 · Answer 4 · answered Apr 03 '22 at 08:51

You could use something like this:

IIF(@Status IS NULL OR @Status = '', '1=1', ' EA.Status = ''' + @Status + ''' ')

Your code could look like this:

Declare @SQL VARCHAR(5000)

SET @SQL = '
    Select ED.Name, ED.Age, ED.Phone, EA.Address 
    FROM EmployeeDetails ED 
    JOIN EmployeeAddress EA ON EA.EmpId = ED.EmpID 
    WHERE
        ED.Name LIKE ''%'+@Name+'%'' AND
        IIF(@Status IS NULL OR @Status = '', '1=1', ' EA.Status = ''' + @Status + ''' ')
    ORDER BY ED.CreatedDate DESC OFFSET ' + CAST((@PageNum * @PageSize) AS NVARCHAR(10)) +' ROWS FETCH NEXT ' + CAST(@PageSize AS NVARCHAR(10)) + ' ROWS ONLY;'

EXEC sp_executesql @SQL

Dynamic IN clause with condition

4 Answers4