SQL Server : Coalesce with multiple values

Question

I am using SQL Server 2008 R2 version. I have a simple table which has multiple columns. One of the column in EmpId which is of type nvarchar(50)

I am writing a stored procedure in which I receive an input which can have one of the following values.

1. Single EmpId: '12345'
2. Multiple EmpId's comma separated: '12345, 56789, 98987'
3. null

What I want:

1. If empid is a single empId just return

   select * 
   from table_name 
   where EmpId  = @empId
   

2. If empid is multiple comma-separated values, just return

   select * 
   from table_name 
   where EmpId in (select * from dbo.splitstring(@empId))
   

3. if empId is null just return

   Select * 
   from table_name
   

   No need for where clause.

To cover all the three condition this is what I am trying:

DECLARE @empId nvarchar(2000)

SET @empId  = '97050001,97050003, 97050004'

SELECT TOP 10 empId 
FROM Employee 
WHERE empId in (COALESCE((select * from dbo.splitstring(@empId)),[empId])) 

I am getting following error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I understand the error. COALESCE() is expecting a single value but when I am getting comma separated values, splitstring function returns multiple values.

I do not want to build a dynamic query, so besides duplicating the code with if else block where I check if empId is null run select * from table_name else run select * from table name where empId in (). What options do I have?

To split comma-separated string into table, I am using this function:

CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
     @returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN
    DECLARE @name NVARCHAR(255)
    DECLARE @pos INT

    WHILE CHARINDEX(',', @stringToSplit) > 0
    BEGIN
        SELECT @pos  = CHARINDEX(',', @stringToSplit)  
        SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

        INSERT INTO @returnList 
            SELECT @name

        SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
    END

    INSERT INTO @returnList
        SELECT @stringToSplit
    RETURN
END

Answer 1

Case 1 and 2 can both be handled for the code you have already written for Case 2. You just need to add an OR condition for Case 3.

select * 
from table_name 
where @empId is NULL
   or EmpId in (select * from dbo.splitstring(@empId))

That being said, IN clauses wrapping select statements are generally bad practice for the following reasons

• They are slower than joins when the select statement returns many rows
• Joins are more idiomatic

In your case, if there are only a few rows returned by splitstring it likely won't make much difference, but the following would be a more general approach to this kind of query.

select * 
from table_name t
left join dbo.splitstring(@empId) s
    on t.EmpId = s.Name
where @empId is NULL
   or s.Name is not NULL

Feel free to check the execution plan for your query and profile to see which one is quicker, though your initial implementation should be fine. To quote Donald Knuth, "premature optimization is the root of all evil."

Update

After double checking the execution plans used in the cases when @empId is null and non null, it looks like the query above will always use the same execution plan, i.e. to join against the contents of the in clause, regardless of whether @empId is null. This is probably not ideal, as pointed out by @m-ali.

To ensure the proper execution plan in each case, you can separate this into two queries:

IF @empId is NULL
    select * 
    from table_name
ELSE
    select * 
    from table_name 
    where EmpId in (select * from dbo.splitstring(@empId))

I've verified the proper execution plan in either case in SSMS.

Disclaimer: I haven't profiled it but the string splitting suggested by @m-ali is also likely faster.

Answer 2

Depends on what you need:-

I do not want to build dynamic query, so besides duplicating the code with if else block where I check if empId is null run select * from table_name else run select * from table name where empId in ()

and for avoiding the duplicate, use the next approach:-

DECLARE @empId nvarchar(2000)
set @empId  = '97050001,97050003,97050004'
if CHARINDEX(',',@empId) > 0 -- multiple Values
begin
    set @empId = '''' +  replace (@empId,',',''',''') +  ''''
end
else if @empId is null
begin
    set @empId = 'select empId from Employee'
end

exec ('select top 10 empId from Employee where empId in (' + @empId + ')' )

This approach handles the three cases:-

1. Passing a single value.
2. Passing multiple Value.
3. Passing a Null.

Answer 3

Try this with a bit more sophisticated version of split string fuction.

CREATE PROCEDURE myProc 
 @EmpId NVARCHAR(50) = NULL
AS
BEGIN
   SET NOCOUNT ON;

DECLARE @Sql NVARCHAR(MAX);

SET @Sql = N' SELECT * FROM Table_Name WHERE 1 = 1'
         + CASE WHEN @EmpId IS NOT NULL THEN 
           N' AND empId  IN ( SELECT  Split.a.value(''.'', ''VARCHAR(100)'') empId 
                                FROM (
                                      SELECT Cast (''<X>'' 
                                                   + Replace(@EmpId, '','', ''</X><X>'') 
                                                   + ''</X>'' AS XML) AS Data
                                ) AS t CROSS APPLY Data.nodes (''/X'') AS Split(a)
                            ) ' ELSE N'' END

 Exec sp_executesql @Sql 
                   ,N'@EmpId NVARCHAR(50)'
                   ,@EmpId 

END 

This version will perform better because

1. More efficient string splitting.
2. Better execution plan because of the parameterised execution plan caching because of the sp_executesql .