Dynamic IN clause with condition-CodePudding

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?

CodePudding user response：

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);

CodePudding user response：

Try adding a dummy entry in IN clause like

...IN (999, ' @STATUS...

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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