Adding Dyanmic In() Conditions in Sql Server

Facing problem for generating SQL Server Query In the Following query dynamic conditions are added to check whether value is null or not

Select  * 
From    tblEmployees 
where   EmployeeName =  Case 
                            When @EmployeeName Is Not 开发者_StackOverflow中文版Null 
                                Then @EmployeeName 
                            Else EmployeeName 
                        End 

But I need to add IN () Conditions and the parameter with in the IN () could be null or blank also ,if the parameter /string which is passed to the IN condition is blank then i donot want to add that condition in the query.

So how can i Achieve this.A helping hand will be very useful for me.

Thanks and Regards, D.Mahesh

Depending on value of your parameter (blank of not), you can create SQL string accordingly.

DECLARE @sqlCommand VARCHAR(1000)
IF(ISNULL(@YourParameter,'')='')
    @sqlCommand = 'your query goes here'
ELSE
    @sqlCommand = 'your query goes here'

and then, run it using dynamic query execution

EXEC (@sqlCommand)

If not dynamic query then,

SELECT ....
FROM ....
WHERE CASE WHEN ISNULL(@YourParameter,'')='' THEN '' ELSE EmployeeName END IN (ISNULL(@YourParameter,''))

See if this works...

I think the Dynamic query is the best solution, however you could put the "IS NULL" and "IS BLANK" condition in OR with your IN clause.

Something like that

Select  * 
From    tblEmployees 
where   @EmployeeName is null or EmployeeName in (@EmployeeName)

When @EmployeeName is null, your IN clause will be ignored

If i get this right you have @EmployeeName = 'Name1,Name2,Name3' and you want to get the employees that is named Name1 or Name2 or Name3, also the variable @EmployeeName can be null or contain an empty string.

Instead of using IN you can split the string @EmployeeName on , and store it in a table variable or temporary table. Then you can use that table in a join against tblEmployees to get the rows you need.

There are a lot of posts in S.O. about how to split a string. Here is one recent variant. Group by sql query on comma joined column

This will work for SQL Server 2005 or later.

declare @EmployeeName varchar(100) = 'Name2,Name3,Name5'

-- Null or empty will have a comma
set @EmployeeName = coalesce(@EmployeeName, '') + ','

-- cteNames splits the string to rows
;with cteNames
as
(
  select
    left(@EmployeeName, charindex(',', @EmployeeName)-1) as Name,
    right(@EmployeeName, len(@EmployeeName)-charindex(',', @EmployeeName)) as EmployeeName
  union all
  select
    left(EmployeeName, charindex(',', EmployeeName)-1) as Name,
    right(EmployeeName, len(EmployeeName)-charindex(',', EmployeeName)) as EmployeeName
  from cteNames
  where charindex(',', EmployeeName) > 1
)
select E.*
from tblEmployees as E
  inner join cteNames as N
    on E.Name = N.Name or
       @EmployeeName = ','

-- @EmployeeName = ',' will give you all names when @EmployeeName is null of empty