Q:

tsql "in" not return anything

i Have this query:
declare @docId as varchar(36)    
declare @totalId AS VARCHAR(500)     
declare @C5 as int    
declare @bool as bit    

set @bool =1    
set @docId='fba18752-1a70-4413-bc3f-9390eaf8a7ef'    
set @totalId='''95B3F02C-8EBD-402E-9721-268A02830678'''+','+'''0211CFA9-1957-4C46-A6BE-16F7B5D8C50F'''

print @totalId

SELECT @C5=COUNT(*) FROM tblSend WHERE senDocId= @docId AND senReceiverDepartmentId IN (@totalId) AND senStatus in (3110,3125,3121) AND senReadDocument IN (0,2)

and has following error:
Msg 8169, Level 16, State 2, Line 15    
Conversion failed when converting from a character string to uniqueidentifier.

but when i put  @totalId value instead of it's variable it return like this:
 SELECT @C5=COUNT(*) FROM tblSend WHERE senDocId= @docId AND senReceiverDepartmentId IN ('95B3F02C-8EBD-402E-9721-268A02830678','0211CFA9-1957-4C46-A6BE-16F7B5D8C50F') AND senStatus in (3110,3125,3121) AND senReadDocument IN (0,2)

why?

A:

Because IN expects a single or comma separated values to match with column values. The value you proved in inside IN is 'SomeX,SomeY'. SQL Server is trying to find  'SomeX,SomeY' value in senReceiverDepartmentId, in process of which it tried to convert 'SomeX,SomeY' to GUID(source column datatype). that resulted in invalid conversion error. 
What you wanted to use in IN is 'SomeX' , 'SomeY' 
What you are actually using is 'SomeX,SomeY'
Try using a table variable and insert ids into it and use it in combination of IN.
    declare @docId as varchar(36)    
    DECLARE @totalId AS VARCHAR(500)   
    declare @C5 as int  
    declare @bool as bit  
    set @bool =1

    set @docId='fba18752-1a70-4413-bc3f-9390eaf8a7ef'

    DECLARE @totalIds TABLE(id varchar(100))

    INSERT INTO  @totalIds 
    VALUES
    ('95B3F02C-8EBD-402E-9721-268A02830678'),
    ('0211CFA9-1957-4C46-A6BE-16F7B5D8C50F')

    print @totalId

    SELECT @C5=COUNT(*) 
    FROM tblSend
    WHERE senDocId= @docId 
      AND senReceiverDepartmentId IN (select id from @totalIds) 
      AND senStatus in (3110,3125,3121) AND senReadDocument IN (0,2)

Hope that makes sense.

