Search by multiple parameter values without using dynamic SQL

This article will demonstrate how to use multi-value paramter in sql without using dynamic SQL.

Hi Folks,

We all have at sometime worked on search and listing pages in our application. We often face situations where we need to search data by multiple values of same parameters (multi-value parameters). For example, we may need to search sales data by selecting multiple cities.

 

In such scenarios, we generally use dynamic SQL in backend to append Ids.

Example:

DECLARE @SQL nvarchar(max)
DECLARE @CityIds nvarchar(max)
SET @SQL = 'SELECT CityId, (SalesAmount) '
SET @SQL = @SQL + 'FROM Orders '
SET @SQL = @SQL + 'WHERE CityId In (' + @CityIds + ') '
SET @SQL = @SQL + 'GROUP BY CityId '
EXEC (@SQL)

In such cases, if the parameter @CityIds is not handled properly, it may led to security concern like 'SQL INJECTION'.

To avoid this and similar issues, we can simple do the following:

DECLARE @CityIds nvarchar(max)
SET @CityIds = ',' + @CityIds + ','
SELECT CityId, (SalesAmount)
FROM Orders
WHERE @CityIds = ',,' OR (@CityIds Like '%,' + Convert(nvarchar(10),CityId) + ',%')
GROUP BY CityId

Both query will yield same results but the second method eliminates the security concern.

Attached is a example that you can execute and check in SQL.