SQL Server - 在视图中使用存储过程结果

Apologies for this seeming like a question that has been asked before, but nothing seemed to be applicable in my searches.

对此表示歉意似乎是之前提出过的问题，但似乎没有什么适用于我的搜索。

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.

在我最近工作的遗留SQL Server数据库中，我遇到了一个*数据库中的视图，该视图旨在为后续的，项目特定的数据库（名为rpt.SubjectInitials）中的参与者创建初始化，这些数据库使用了一系列联合，因此不得不不断更新。

Ex.

防爆。

 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)

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.

显然，每当新项目上线并且旧项目退役时，这就会引发问题。我创建了一个存储过程，它动态地遍历所有活动项目，并从项目中前面提到的“vwMergeLetterFields”视图中提取必要的信息。存储过程使用游标并将结果插入临时表，并在末尾运行选择以获取结果。

Ex.

防爆。

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 > 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

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).

我知道使用游标通常是不受欢迎的，但我们有定期活动和非活动的项目，它们几乎都共享很多功能（我对动态提取/更新数据的代码持开放态度）。不幸的是，虽然上面的代码适用于从该数据库中提取数据的系统的大多数部分，但它不适用于引用原始视图的视图（rpt.SubjectInitials）。

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: http://www.sommarskog.se/share_data.html and really can't find how to make my code work with any of the suggested solutions. Any further guidance would be a lifesaver.

所以我的问题归结为：我需要能够将存储过程的结果转换为可以在用于引用rpt.SubjectInitials的视图中使用的形式。我已经看过这个：http：//www.sommarskog.se/share_data.html并且真的找不到如何让我的代码与任何建议的解决方案一起工作。任何进一步的指导都将成为救星。

Thank you

谢谢

1 个解决方案

#1

The only solution I am aware of is by using OPENQUERY or OPENROWSET

我所知道的唯一解决方案是使用OPENQUERY或OPENROWSET

SELECT * 
FROM OPENQUERY([.], '[MyDatabaseName].[dbo].[usp_SubjectInitials]') query
INNER JOIN States ON query.Code = States.Code

This blog post was very helpful (http://www.kodyaz.com/articles/how-to-sql-select-from-stored-procedure-using-openquery-openrowset.aspx).

这篇博客文章非常有用（http://www.kodyaz.com/articles/how-to-sql-select-from-stored-procedure-using-openquery-openrowset.aspx）。

#1