在视图中执行存储过程?

时间:2022-09-21 07:38:11

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.

几个星期前我开始为一家公司工作,并继承了疯狂的数据库。我目前正在设计新系统以取代旧系统。之前的开发人员创建了大量完全相同的视图,唯一的区别是WHERE子句中的条件。在我尝试清理源代码时,我试图创建一个基于给定条件返回表的存储过程,因此我可以将250个视图压缩到一个存储过程中。

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

有没有办法可以执行我在当前现有视图中创建的存储过程,所以我不必修改它们的前端(通过ODBC连接到SQL Server的一系列访问数据库)?

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

4 个解决方案

#1


2  

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

对于后者,您必须确保使用内联函数。否则,将对动态生成的结果集执行任何后续子句,如WHERE,GROUP BY和ORDER BY。因此,您不会受益于索引搜索等。这可能会对性能产生巨大影响。

#2


1  

There are two ways to do this, both with their pros and cons:

有两种方法可以做到这一点,两者都有其优点和缺点:

  1. Use OPENROWSET / OPENQUERY. These allow you do do a SELECT on anything you want, but it might have security implications that you don't like. That might not be an issue if this is a short-term solution and afterwards you can undo the allowing of "Ad Hoc Distributed Queries". But this is the easiest to set up, especially if the result sets vary between the procs (assuming that there are multiple procs).

    使用OPENROWSET / OPENQUERY。这些允许您对所需的任何内容执行SELECT,但它可能具有您不喜欢的安全隐患。如果这是一个短期解决方案,那么这可能不是问题,之后您可以撤消允许“Ad Hoc Distributed Queries”。但这是最容易设置的,特别是如果结果集在procs之间有所不同(假设有多个procs)。

  2. Write a SQLCLR TVF that executes the procedure. This can be done in SAFE mode if the Stored Procedures are read-only (i.e. no INSERT / UPDATE / DELETE statements and most likely no CREATE #Tmp statements). I wrote an article showing an example of this: Stairway to SQLCLR Level 2: Sample Stored Procedure and Function

    编写执行该过程的SQLCLR TVF。如果存储过程是只读的(即没有INSERT / UPDATE / DELETE语句,很可能没有CREATE #Tmp语句),则可以在SAFE模式下完成。我写了一篇文章,展示了一个例子:SQLCLR的阶梯2级:样本存储过程和函数

#3


0  

If you want to do some heavy lifting, perhaps manipulating table variables, using while loops and so on, then use a function and reference that from your view.

如果你想做一些繁重的工作,也许是操纵表变量,使用while循环等,那么使用你的视图中的函数和引用。

You can't use a stored procedure because stored procs are allowed to edit your data - as such if you could reference them from a view, then a select could have side effects - which is not allowed.

您不能使用存储过程,因为允许存储过程编辑您的数据 - 如果您可以从视图中引用它们,那么选择可能有副作用 - 这是不允许的。

#4


0  

You can easily convert procedures like this into inline TVFs, also known as parameterized views. The body of the view will remain identical to what you have in the procedure.

您可以轻松地将此类过程转换为内联TVF,也称为参数化视图。视图的主体将与过程中的内容保持一致。

Selecting from views/TVFs is different than executing stored procedures, so you might have to change the front end anyway. In any case, someone will have to supply values for the parameters.

从视图/ TVF中选择与执行存储过程不同,因此您可能无论如何都必须更改前端。无论如何,有人必须提供参数的值。

#1


2  

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

对于后者,您必须确保使用内联函数。否则,将对动态生成的结果集执行任何后续子句,如WHERE,GROUP BY和ORDER BY。因此,您不会受益于索引搜索等。这可能会对性能产生巨大影响。

#2


1  

There are two ways to do this, both with their pros and cons:

有两种方法可以做到这一点,两者都有其优点和缺点:

  1. Use OPENROWSET / OPENQUERY. These allow you do do a SELECT on anything you want, but it might have security implications that you don't like. That might not be an issue if this is a short-term solution and afterwards you can undo the allowing of "Ad Hoc Distributed Queries". But this is the easiest to set up, especially if the result sets vary between the procs (assuming that there are multiple procs).

    使用OPENROWSET / OPENQUERY。这些允许您对所需的任何内容执行SELECT,但它可能具有您不喜欢的安全隐患。如果这是一个短期解决方案,那么这可能不是问题,之后您可以撤消允许“Ad Hoc Distributed Queries”。但这是最容易设置的,特别是如果结果集在procs之间有所不同(假设有多个procs)。

  2. Write a SQLCLR TVF that executes the procedure. This can be done in SAFE mode if the Stored Procedures are read-only (i.e. no INSERT / UPDATE / DELETE statements and most likely no CREATE #Tmp statements). I wrote an article showing an example of this: Stairway to SQLCLR Level 2: Sample Stored Procedure and Function

    编写执行该过程的SQLCLR TVF。如果存储过程是只读的(即没有INSERT / UPDATE / DELETE语句,很可能没有CREATE #Tmp语句),则可以在SAFE模式下完成。我写了一篇文章,展示了一个例子:SQLCLR的阶梯2级:样本存储过程和函数

#3


0  

If you want to do some heavy lifting, perhaps manipulating table variables, using while loops and so on, then use a function and reference that from your view.

如果你想做一些繁重的工作,也许是操纵表变量,使用while循环等,那么使用你的视图中的函数和引用。

You can't use a stored procedure because stored procs are allowed to edit your data - as such if you could reference them from a view, then a select could have side effects - which is not allowed.

您不能使用存储过程,因为允许存储过程编辑您的数据 - 如果您可以从视图中引用它们,那么选择可能有副作用 - 这是不允许的。

#4


0  

You can easily convert procedures like this into inline TVFs, also known as parameterized views. The body of the view will remain identical to what you have in the procedure.

您可以轻松地将此类过程转换为内联TVF,也称为参数化视图。视图的主体将与过程中的内容保持一致。

Selecting from views/TVFs is different than executing stored procedures, so you might have to change the front end anyway. In any case, someone will have to supply values for the parameters.

从视图/ TVF中选择与执行存储过程不同,因此您可能无论如何都必须更改前端。无论如何,有人必须提供参数的值。

相关文章