Comment puis-je transformer une procédure stockée en une vue ?

Question

J'ai cette procédure stockée qui extrait les données de 4 tables et les classe par 4 colonnes, mais j'ai besoin d'une vue avec le même résultat (bien sûr sans paramètre). Est-ce possible ? Je sais que je ne peux pas utiliser une table temporaire dans la vue, mais j'ai une solution !

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, ','))

Answer 1

Utilisez un Expression de table commune (CTE) au lieu d'une table temporaire, et utilisez le CTE là où vous utilisez actuellement la table temporaire.

Comme ça :

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

De cette façon, vous pouvez créer une seule requête et la regrouper dans votre vue.