CODEWITHSUNDEEP
sql-serverstored-procedures
koda5
koda5

Reputation: 43

How I can transform a stored procedure to a view?

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

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

Upvotes: 3

Views: 147

Answers (1)

TT.
TT.

Reputation: 16137

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

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.

Upvotes: 3

Related Questions