title: SQL Server - Using stored procedure results in a view
body: <p>Apologies for this seeming like a question that has been asked before, but nothing seemed to be applicable in my searches.  </p>
  
  <p>In a legacy SQL Server database I was working on recently I came across a view in a central database meant to create initials for participants in subsequent, project-specific databases (named rpt.SubjectInitials) that was using a series of unions and thus had to be constantly updated.  </p>
  
  <p>Ex.</p>
  
  <pre><code> SELECT        SubjectID, FirstName, MiddleInitial, LastName, Initials, 1 AS Project
  FROM            Switch.dbo.vwMergeLetterFields
  WHERE        (SubjectID IS NOT NULL)
  UNION
  SELECT        SubjectID, FirstName, MiddleInitial, LastName, Initials, 2 AS Project
  FROM            Mars.dbo.vwMergeLetterFields
  WHERE        (SubjectID IS NOT NULL)
  </code></pre>
  
  <p>Obviously this was causing issues whenever a new project came online and old projects were retired.  I created a stored procedure that dynamically goes through all the active projects and pulls the necessary information from the aforementioned "vwMergeLetterFields" views in the projects.  The stored procedure uses a cursor and inserts the results into a temporary table and runs a select at the end to get the results.</p>
  
  <p>Ex.</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[usp_SubjectInitials]
  AS
  Begin
  
  Declare @Projectname as nvarchar(20)
  DECLARE @fName as nvarchar(max)
  
  IF OBJECT_ID('tempdb..#TblWData')Is Not Null Drop Table #TblWData
  CREATE TABLE [dbo].[#TblWData](SubjectID int, FirstName nvarchar(50), MiddleInitial nvarchar(10), LastName nvarchar(50), Initials nvarchar(10), Project nvarchar(25)) ON [PRIMARY]
  
  
  --Declares the cursor and gives it a name
  DECLARE SubjectInitials CURSOR 
  
  LOCAL SCROLL STATIC
  
  FOR
  
  SELECT dbname FROM [Assessments].[dbo].[rtblProject]
    where active = 1 and project &gt; 1 
  
  --Executes the cursor
  OPEN SubjectInitials 
  
  FETCH NEXT FROM SubjectInitials
  
     INTO @Projectname
  
  WHILE @@FETCH_STATUS = 0
  
  BEGIN
  
  set @fname = 'IF(EXISTS(SELECT * FROM ' + @Projectname +'.INFORMATION_SCHEMA.Views WHERE TABLE_SCHEMA = ''dbo'' AND TABLE_NAME = ''vwMergeLetterFields'')) 
  BEGIN
  insert into #TblWData(SubjectID, FirstName, MiddleInitial, LastName, Initials, Project)
  SELECT       distinct SubjectID, FirstName, MiddleInitial, LastName, Initials, Project
  FROM            ' + @Projectname +'.dbo.vwMergeLetterFields
  WHERE        (SubjectID IS NOT NULL)
  END'
  
  
  exec sp_executesql @fname 
  
  --Tells the cursor to move on to the next line in the results (i.e. the next table)
   FETCH NEXT FROM SubjectInitials
  
     INTO @Projectname
    -- PRINT @Projectname 
  END
  
  --close the cursor
  CLOSE SubjectInitials 
  
  --Closes the cursor
  DEALLOCATE SubjectInitials 
  
  End
  
  select * from #TblWData
  </code></pre>
  
  <p>I know that using a cursor is generally frowned upon but we have projects go active and inactive regularly and they almost all share a lot of functionality (I'm open to code that dynamically pulls/updates data).  Unfortunately while the above code works for most parts of the system that pulls data from this database, it doesn't apply for the views that reference the original view (rpt.SubjectInitials).  </p>
  
  <p>So my issue boils down to this: I need to be able to get the results of the stored procedure into a form that can be used in the views that used to reference rpt.SubjectInitials.  I've looked through this: <a href="http://www.sommarskog.se/share_data.html" rel="nofollow noreferrer">http://www.sommarskog.se/share_data.html</a> and really can't find how to make my code work with any of the suggested solutions.  Any further guidance would be a lifesaver.</p>
  
  <p>Thank you</p>