title: result of one stored procedure to input for Query showing null
body: <p>I wrote two stored procedures</p>
  <pre class="lang-sql prettyprint-override"><code>ALTER PROCEDURE [dbo].[GetPhysicalChildNodesAsString]
      -- Add the parameters for the stored procedure here
      @Book varchar(50),
      @Vertical varchar(50)
  AS
  BEGIN
      DECLARE @results nvarchar(max)
  ;WITH cte AS 
   (
    SELECT a.BookID, a.ParentBookID, a.BookName
    FROM DimBook a
    WHERE BookName = @Book
    UNION ALL
    SELECT a.BookID, a.ParentBookID, a.BookName
    FROM DimBook a JOIN cte c ON a.ParentBookID = c.BookID
    and HighPortfolioID = ( select PortfolioID from portfolioMaster where PortfolioName = @Vertical and PortfolioType='HighPortfolio')
    )
    select @results = coalesce(@results + ',', '')+'''' +  convert(varchar(max),BookName)+''''
    from cte Where Len(BookName) &lt; 20
  
    select @results as Book
  
  END
  </code></pre>
  <p>Above stored Proc returns below string</p>
  <pre class="lang-none prettyprint-override"><code>'Physical','Anish Vohra','Sandeep Bajoria','Nirav Desai','Sushil Mohta','Sahil Pasad','G R Poddar','Direct','Sales Broker','Internal Transfer','Sprint','Murji Meghan','Intra Oils &amp; Fats','GGN','GGN1','GGN2','Book1','Book2','Book 3','Book4','Book5','Comglobal','Sunvin','PVOC','Afro Asian'
  </code></pre>
  <pre class="lang-sql prettyprint-override"><code>DECLARE @BooksList varchar(max) 
  DECLARE @t table(BookNames varchar(max) )
     INSERT @t(BookNames)
     EXEC @BooksList = GetPhysicalChildNodesAsString 'Physical','Enterprise'
     
     SELECT @BooksList = BookNames FROM @t
  
     select @BooksList as 'books'
  
     select * from DimBook where BookName IN(select @BooksList as 'books') ----&gt; reults only header of table Only. But if pass result string directly like below it is working. can you help me for why above query is not working
  
     select * from DimBook where BookName IN('Physical','Anish Vohra','Sandeep Bajoria','Nirav Desai','Sushil Mohta','Sahil Pasad','G R Poddar','Direct','Sales Broker','Internal Transfer','Sprint','Murji Meghan','Intra Oils &amp; Fats','GGN','GGN1','GGN2','Book1','Book2','Book 3','Book4','Book5','Comglobal','Sunvin','PVOC','Afro Asian') ----&gt; getting correctly.
  </code></pre>