如何从视图中使用动态SQL调用过程
我正在尝试创建一个视图，该视图调用启动时的过程，以计算以下子句中使用的2个变量。查询运行，但不会作为视图保存，因为不允许出现过程调用。我研究了这一点，并发现了有关使用功能的一些建议，但是在尝试时，我会发现函数中不允许动态SQL的错误。 有没有办法做我正在尝试的事情？
代码段：
CALL p_GetOutlierLimits('ROI_Imputed_Percent','DB1',@ClassicLowerROIPct, @ClassicUpperROIPct);
CALL p_GetOutlierLimits('576_VMC_Sol_Savings_Pct','DB2',@vmctcoLower149Pct, @vmctcoUpper149Pct);
USE Database;
SELECT
{CODE}
from TABLE
WHERE ROI_Imputed_Percent BETWEEN @ClassicLowerROIPct AND @ClassicUpperROIPct;
I am trying to create a view that calls a procedure at initiation to compute 2 variables to be used in the WHERE clause. The query runs but will not save as a view as it appears procedure calls are not allowed. I researched this and found some suggestions about using a Function, but in trying that, I get errors that dynamic SQL is not allowed in a function.
Is there a way to do what I am attempting?
Code snippet:
CALL p_GetOutlierLimits('ROI_Imputed_Percent','DB1',@ClassicLowerROIPct, @ClassicUpperROIPct);
CALL p_GetOutlierLimits('576_VMC_Sol_Savings_Pct','DB2',@vmctcoLower149Pct, @vmctcoUpper149Pct);
USE Database;
SELECT
{CODE}
from TABLE
WHERE ROI_Imputed_Percent BETWEEN @ClassicLowerROIPct AND @ClassicUpperROIPct;
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我通过从视图中删除呼叫，并从访问视图的另一个过程中执行这些呼叫来找到另一种方法。
I found another way to do this by removing the calls from the view and just performing them from another procedure that accesses the view.