Execute a Stored Procedure Inside a View?

I started working for a company a few weeks ago, and have inherited a crazy mess of databases. I’m currently working on designing the new systems to replace their older ones. The previous developer created a ton of views that are entirely identical, with the only differences being the conditions within the WHERE clause. In my attempt to cleanup the source, I was attempting to create a stored procedure that returns the table based on the given conditions, so I can compact 250 views into a single stored procedure.

Honestly, the only reason I’m doing this is so when I start transitioning over to the newer databases and front-ends, I have some pre-made stored procedures to use rather than a jumbled mess of views.

Is there any way that I can execute the stored procedures I created inside of the currently existing views, so I don’t have to modify their front end (A series of access databases connected to SQL Server through ODBC)?

My stored procedure looks something like this:

CREATE PROCEDURE BusDuctQueries.CMSF_Current_Mod @ModNumber VARCHAR(255)
AS
   SELECT        [Date], [Name], [Mod], [Cell], [Row], [Section],
    [Phase A Breaker Amps], [Phase B Breaker Amps], [Phase C Breaker Amps], 
    [Phase A Actual Amps], [Phase B Actual Amps], [Phase C Actual Amps], 
    [PDU # 1], [Bus Duct # 1], 
    [Bus Duct # 1 Phase A Current Load], [Bus Duct # 1 Phase B Current Load], [Bus Duct # 1 Phase C Current Load], 
    [PDU # 2], [Bus Duct # 2], 
    [Bus Duct # 2 Phase A Current Load], [Bus Duct # 2 Phase B Current Load], [Bus Duct # 2 Phase C Current Load], 
    [Sort 1], [Sort 2], [Average Load], [Percent Used], [Percent Remaining], [KW Used], 
    CONVERT(NUMERIC(18, 2), [Derated KW Left]) AS [Derated KW Left], 
    CONVERT(NUMERIC(18, 2), [True KW Left]) AS [True KW Left], 
    CASE WHEN [PDU # 1] LIKE '%A%' THEN 1 ELSE 2 END AS UPSSort
FROM            BusDuctQueries.[CMSF Current]
WHERE        ([Mod] = @ModNumber) AND  (Cell NOT LIKE '%WP%')

Answer

You cannot call a stored proc from inside a view. It is not supported. However you can make views call other views, or table-valued user-defined functions.

For the latter you must make sure that you’re using inline functions. Otherwise, any subsequent clauses like WHERE, GROUP BY and ORDER BY to will be performed on the dynamically produced resultset instead. Thus, you won’t benefit from index searches and the likes. This may have a huge impact on performance.

Advertisement

Answer