title: SQL use function returned string in select where in(..) query
body: <p>I have a function that returns a string with id's connected by comma, something like that:</p>
  
  <pre><code>CREATE FUNCTION [dbo].[fGetChildIdByPId] 
  (
      @ID INT         
  )
  RETURNS VARCHAR(MAX)
  AS
  BEGIN 
      DECLARE @RET VARCHAR(1000)
      SET @RET = ''
  
      DECLARE CUR CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR 
          SELECT ID FROM tControl WHERE parentID = @ID 
      OPEN CUR
  
      FETCH FROM CUR INTO @ID
      WHILE @@FETCH_STATUS = 0 
      BEGIN
          SET @RET = @RET + CAST(@ID AS VARCHAR(10)) + ',' 
          + dbo.fGetChildIdByPId(@ID) 
          FETCH NEXT FROM CUR INTO @ID
      END
      CLOSE CUR
      DEALLOCATE CUR   
  
      RETURN  @RET 
  END
  </code></pre>
  
  <p>In another function I want to use the string returned from previous function in another select query like that:</p>
  
  <pre><code>CREATE FUNCTION ShouldOutput
  ( 
      @id int,  
      @gid int
  )
  returns int
  as
  begin
       declare @out int
  
       if exists(SELECT * FROM tControl 
           where id in (select [dbo].[fGetChildIdByPId](@id)) 
           and summaryIndex=1 and goodId = @gid)
           set @out = 1
       else 
          set @out = 0
  return @out
  end
  </code></pre>
  
  <p>But of course, there is the problem because the value of select [dbo].fGetChildIdByPId can't be cast to integer and can't be used in a query. I thought of using EXEC or sp_executesql but I can't use them in a function, only in stored procedures but I need a function, because I need to use the returned 1 or 0 value in another sql query. What is the easiest way to make the mentioned query work?</p>