title: How to check if a table valued parameter is empty or not inside of a where clause?
body: <p>I am writing a function where I am passing a table valued parameter.
  In some of the cases table valued parameter can be empty.</p>
  
  <p>So, my function looks like below-</p>
  
  <pre><code>CREATE FUNCTION [dbo].[testTableValueParam]
  (   
      @created_date datetime = null
      ,@Ids dbo.IdList readonly
  )
  RETURNS TABLE 
  AS
  RETURN 
  (
       SELECT top 10
              name
              ,scores
              ,mgr_name
  
          from dbo.employee
          where
              created_date = @created_date
              and 
              employeeId in (select empid from @Ids)           --ignore this condition when @Ids is empty. 
  )
  </code></pre>
  
  <p>My table type is created as below-  </p>
  
  <pre><code>CREATE TYPE [dbo].[IdList] AS TABLE(
      [empid] [nvarchar](11) NULL
  )
  </code></pre>
  
  <p>I am calling my function from a c# code.<br>
  There are cases when the table value parameter will be empty and in those cases, when the table value parameter is empty, i want to ignore the condition in where clause.  </p>
  
  <p>I went through some of the links while searching my answer and the answers suggested in earlier posts didn't fix my problem.  </p>
  
  <p>So, right now, when @Ids parameter is empty, it gives me no record.<br>
  In some of the post they suggested not to pass a parameter for table value at all, and it will automatically treat it as an empty table.<br>
  But I have cases when I need to pass the parameter with data.<br>
  Some of the answers suggested, using <code>if exist(select 1 from @Ids)</code><br>
  But, I can not use if exist in my where clause.  </p>
  
  <p>Please provide any suggestions.<br>
  Your responses are much appreciated.  </p>
  
  <p>Thanks.</p>