title: SELECT from stored procedure inside a view
body: <p>Can someone enlighten me as to why this will not work?</p>
  
  <p>I have a stored procedure that returns a table:
  calling it like so works fine:</p>
  
  <pre><code>EXECUTE dbo.sp_Get_Total_Parcels_Paid_Data
  </code></pre>
  
  <p>However, I need to use this inside of a view, and cannot get it to work.</p>
  
  <pre><code>Select * from dbo.sp_Get_Total_Parcels_Paid_Data()
  </code></pre>
  
  <p>Error: </p>
  
  <blockquote>
    <p>Invalid object name 'dbo.sp_Get_Total_Parcels_Paid_Data()</p>
  </blockquote>
  
  <p>SP Code:</p>
  
  <pre><code>create PROCEDURE [dbo].[sp_Get_Total_Parcels_Paid_Data]
  AS
  BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;
  
  declare @Sql1 nvarchar(max)
  ,@Sql2 nvarchar(max)
  ,@Sql3 nvarchar(max)
  
  declare @TallyResults table
  (EFOLDERID nvarchar(31) null    
  ,inv_bank_cd int null
  ,inv_cd int null
  ,inv_group_cd int null
  ,intLoanNumber int null
  ,other_text nvarchar(250) null
  ,tallyCount int
  ,eidMgrId nvarchar(31))
  
  
  select @Sql1 = 'Select ' + tallydefinition from jobfunctions where jobfuncid = 44
  select @Sql2 = 'Select ' + tallydefinition from jobfunctions where jobfuncid = 45
  select @Sql3 = 'Select ' + tallydefinition from jobfunctions where jobfuncid = 206
  
  insert into @TallyResults exec sp_executesql @Sql1
  insert into @TallyResults exec sp_executesql @Sql2
  insert into @TallyResults exec sp_executesql @Sql3
  
  select * from @TallyResults
  
  END
  </code></pre>