Search code examples
sql-serversql-server-2012sql-server-2014

Dynamic WHERE condition SQL SERVER

I have the below requirement. When FirstName present I should return the only condtionFirstName when LastName present I should return the only condtionlastName when First and email both are present I should return condtionFirstName + condtionEmail.

Basically whichever value is present I should return that condition, If 1 value 1 condition, if 1 and 2 then condition 1 and 2, if 1 and 3 then condition 1 and 3, if only 3 then condition 3.

Kindly help me to get this logic.

DECLARE @FirstName      NVARCHAR(100)
DECLARE @LastName       NVARCHAR(100)
DECLARE @Email          NVARCHAR(200)
DECLARE @condtionFirstName NVARCHAR(200)
DECLARE @condtionlastName NVARCHAR(200)
DECLARE @condtionEmail NVARCHAR(200)

SET @FirstName = 'JOhn'
SET @LastName  = 'David'
SET @Email     = '[email protected]'

SET @condtionFirstName = ' AND FirstName = ' + '''' + @FirstName + ''''  
SET @condtionlastName =  ' AND LastName = ' + '''' + @LastName + ''''
SET @condtionEmail = ' AND Email = ' + '''' + @Email + ''''
Solution

  • This is what I've long called "the kitchen sink" - you want a single query that can support any combination of search criteria. Some thoughts:

    • Stop trying to concatenate user input with executable strings - this is dangerous and error-prone. These should always be passed in as explicitly-typed parameters.
    • You can build a single string containing all the conditions instead of trying to create a new condition string for every possible condition.
    • You can declare and pass parameters to sp_executesql even if they don't all end up in the dynamic SQL statement. This is just like declaring a local variable and not using it.
    • e-mail addresses can be 320 characters long. If you only support 200, that could bite you.

    Sample:

    DECLARE @FirstName         nvarchar(100),
        @LastName          nvarchar(100),
        @Email             nvarchar(320),
        @conditions        nvarchar(max) = N'';

SET @FirstName = N'John';
SET @LastName  = N'David';
SET @Email     = N'[email protected]';

SET @conditions += CASE WHEN @FirstName IS NOT NULL THEN
      N' AND FirstName = @FirstName' ELSE N'' END
  + CASE WHEN @LastName IS NOT NULL THEN
      N' AND LastName = @LastName' ELSE N'' END
  + CASE WHEN @Email IS NOT NULL THEN
      N' AND Email = @Email' ELSE N'' END;

DECLARE @sql nvarchar(max) = N'SELECT ... FROM dbo.table 
        WHERE 1 = 1' + @conditions;

PRINT @sql; -- try this when populating or not populating each of
            -- @FirstName, @LastName, @Email

EXEC sys.sp_executesql @sql, 
  N'@FirstName nvarchar(100), @LastName nvarchar(100), @Email nvarchar(320)', 
  @FirstName, @LastName, @Email;

    More details: