如何将存储过程转换为视图?

I have this stored procedure which pulls data from 4 tables and ranks them by 4 columns, but I need a view with the same result (of course without parameter). Is this possible? I know that I can not use a temp table in view, any solution?!

我有这个存储过程，它从4个表中提取数据并对它们进行4列排序，但是我需要一个结果相同的视图(当然没有参数)。这是可能的吗?我知道我不能在视图中使用临时表，任何解决方案?!

ALTER PROC pr_GetChildrenByPersonID
    @pePersonIDs varchar(8000)
AS
    SELECT 
        *,
        RANK() OVER (PARTITION BY pbsPersonID ORDER BY psbPersonSiblingID ASC) AS CustomRank
    INTO 
        #TempPersonSibling
    FROM 
        PersonSibling
    WHERE 
        pbsSiblingTypeID = 2
        AND pbsPersonID IN (SELECT item 
                            FROM dbo.fn_ParseStrListIdentity(@pePersonIDs, ','))

    SELECT
        pePersonID,
        ISNULL((SELECT pbsName 
                FROM #TempPersonSibling   
                WHERE pbsSiblingTypeID = 2  
                  AND pbsPersonID = pepersonID 
                  AND CustomRank = 1), '') AS ChildrenName1,
        ISNULL((SELECT pbsAge  
                FROM #TempPersonSibling   
                WHERE pbsSiblingTypeID = 2  
                  AND pbsPersonID = pepersonID 
                  AND CustomRank = 1), '') AS ChildrenAge1,
        ISNULL((SELECT dbo.fn_GetCountry(pbsPlaceOfBirth)  
                FROM #TempPersonSibling   
                WHERE pbsSiblingTypeID = 2  
                  AND pbsPersonID = pepersonID 
                  AND CustomRank = 1), '') AS ChildrenCountryOfBirth1,
        ISNULL((SELECT dbo.fn_GetCountry(pbsPresentStay)  
                FROM #TempPersonSibling   
                WHERE pbsSiblingTypeID = 2  
                  AND pbsPersonID = pepersonID 
                  AND CustomRank = 1), '') AS ChildrenCountryOfResidence1,
        ISNULL((SELECT pbsName 
                FROM #TempPersonSibling   
                WHERE pbsSiblingTypeID = 2  
                  AND pbsPersonID = pepersonID 
                  AND CustomRank = 2), '') AS ChildrenName2,
        ISNULL((SELECT pbsAge  
                FROM #TempPersonSibling   
                WHERE pbsSiblingTypeID = 2  
                  AND pbsPersonID = pepersonID 
                  AND CustomRank = 2), '') AS ChildrenAge2,
        ISNULL((SELECT dbo.fn_GetCountry(pbsPlaceOfBirth)  
                FROM #TempPersonSibling   
                WHERE pbsSiblingTypeID = 2  
                  AND pbsPersonID = pepersonID 
                  AND CustomRank = 2), '') AS ChildrenCountryOfBirth2,
        ISNULL((SELECT dbo.fn_GetCountry(pbsPresentStay)  
                FROM #TempPersonSibling   
                WHERE pbsSiblingTypeID = 2  
                  AND pbsPersonID = pepersonID 
                  AND CustomRank = 2), '') AS ChildrenCountryOfResidence2,
        ISNULL((SELECT pbsName 
                FROM #TempPersonSibling   
                WHERE pbsSiblingTypeID = 2  
                  AND pbsPersonID = pepersonID 
                  AND CustomRank = 3), '') AS ChildrenName3,
        ISNULL((SELECT pbsAge  
                FROM #TempPersonSibling   
                WHERE pbsSiblingTypeID = 2  
                  AND pbsPersonID = pepersonID 
                  AND CustomRank = 3), '') AS ChildrenAge3,
        ISNULL((SELECT dbo.fn_GetCountry(pbsPlaceOfBirth)  
                FROM #TempPersonSibling   
                WHERE pbsSiblingTypeID = 2  
                  AND pbsPersonID = pepersonID 
                  AND CustomRank = 3), '') AS ChildrenCountryOfBirth3,
        ISNULL((SELECT dbo.fn_GetCountry(pbsPresentStay)  
                FROM #TempPersonSibling   
                WHERE pbsSiblingTypeID = 2  
                  AND pbsPersonID = pepersonID 
                  AND CustomRank = 3), '') AS ChildrenCountryOfResidence3,
        ISNULL((SELECT pbsName 
                FROM #TempPersonSibling   
                WHERE pbsSiblingTypeID = 2  
                  AND pbsPersonID = pepersonID 
                  AND CustomRank = 4), '') AS ChildrenName4,
        ISNULL((SELECT pbsAge  
                FROM #TempPersonSibling   
                WHERE pbsSiblingTypeID = 2  
                  AND pbsPersonID = pepersonID 
                  AND CustomRank = 4), '') AS ChildrenAge4,
        ISNULL((SELECT dbo.fn_GetCountry(pbsPlaceOfBirth)  
                FROM #TempPersonSibling   
                WHERE pbsSiblingTypeID = 2  
                  AND pbsPersonID = pepersonID 
                  AND CustomRank = 4), '') AS ChildrenCountryOfBirth4,
        ISNULL((SELECT dbo.fn_GetCountry(pbsPresentStay)  
                FROM #TempPersonSibling   
                WHERE pbsSiblingTypeID = 2  
                  AND pbsPersonID = pepersonID 
                  AND CustomRank = 4), '') AS ChildrenCountryOfResidence4
    FROM 
        Person
    WHERE 
        pePersonID IN (SELECT item 
                       FROM dbo.fn_ParseStrListIdentity(@pePersonIDs, ','))

1 个解决方案

#1

Use a Common Table Expression (CTE) instead of a temporary table, and use the CTE where you are using the temporary table now.

使用公共表表达式(CTE)代替临时表，并在使用临时表的地方使用CTE。

Like so:

像这样:

WITH 
    TempPersonSibling AS (
        -- the select statement that creates the temp table here
        -- without the INTO clause
    )
SELECT
    pePersonID,
    ISNULL((SELECT pbsName FROM TempPersonSibling WHERE pbsSiblingTypeID = 2  AND pbsPersonID = pepersonID AND CustomRank = 1),'') AS ChildrenName1,
    -- ..., rest of the query is similar
FROM
    Person
WHERE
    -- ...

This way you can create a single query and package that in your view.

这样，您就可以在视图中创建一个查询和包。

#1