如何从TVF中调用存储过程？

I'd like to utilise an already written stored procedure having parameters and temp tables for a report with Power BI (DirectQuery).

我想利用已经编写的具有参数和临时表的存储过程来使用Power BI(DirectQuery)进行报告。

I've tried to do so via table-valued function as shown below:

我试图通过表值函数来实现,如下所示:

CREATE FUNCTION [rpt].[ufnPBI0002_KPI]
    (@YearMonth nvarchar(20), -- = '2017-12'
     @Products  nvarchar(50), -- = '2,7,8'
     @Regions   nvarchar(50) -- = '1'
    )

-----------------------------------------------------------------------
-- Return type
-----------------------------------------------------------------------

RETURNS TABLE

-----------------------------------------------------------------------
-- Begin
-----------------------------------------------------------------------

AS

BEGIN

    -----------------------------------------------------------------------
    -- Declare return table
    -----------------------------------------------------------------------

    DECLARE @Result AS TABLE
        (
             SalesRegionId                          INT
            ,TotalCAcount                           INT
            ,EffectiveCAcount                       INT
            ,NewCA                                  INT
            ,Plan_Total_CA_count                    INT
            ,Plan_eCA_Count_All                     INT
            ,Plan_CA_Intake                         INT
            ,Plan_CA_PJ_count                       INT
            ,Plan_NV_Prod_PJ                        INT
            ,Plan_Avg_NV_All_per_eCA                INT
            ,BusinessLoanSpecialistCount            INT
            ,ProfiJuniors                           INT
            ,ActiveCAcount                          INT
            ,TerminatedCAcount                      INT
            ,NewNV                                  INT
            ,NewNVPJ                                INT
            ,AvgNVeCA                               INT
            ,AvgNVProfiJuniors                      INT
        )

    --------------------------------------------------------------------------
    -- Handle stored procedure result
    --------------------------------------------------------------------------

    INSERT INTO @Result

        EXEC [rpt].[MR0002_KPI] @YearMonth, @Products, @Regions

    --------------------------------------------------------------------------
    -- Return the result
    --------------------------------------------------------------------------

    SELECT * FROM @Result

    RETURN;

-----------------------------------------------------------------------
-- END
-----------------------------------------------------------------------

END

Which returns

Msg 102, Level 15, State 31, Procedure ufnPBI0002_KPI, Line 68 [Batch Start Line 0]
Incorrect syntax near 'BEGIN'

消息102,级别15,状态31,过程ufnPBI0002_KPI,行68 [批处理开始行0]'BEGIN'附近的语法不正确

I think it would not be possible to call a stored procedure from a TFV, but would really appreciate any hints, how to utilise parametric stored procedure with PBI.

我认为从TFV调用存储过程是不可能的,但是非常感谢任何提示,如何利用PBI的参数存储过程。

2 个解决方案

#1

You can not call stored procedures from within a Function

您无法从函数内调用存储过程

User-defined functions cannot call a stored procedure, but can call an extended stored procedure.

用户定义的函数不能调用存储过程,但可以调用扩展存储过程。

Change the function to a stored procedure

将该函数更改为存储过程

#2

When it comes to SSRS data sources, you do not have to use a function in your case, bit can just directly use a stored procedure:

对于SSRS数据源,您不必在您的情况下使用函数,bit可以直接使用存储过程:

 EXEC [rpt].[MR0002_KPI] @YearMonth, @Products, @Regions

Just refresh a dataset, it should be able to detect available columns returned by SP.

只需刷新数据集,它应该能够检测SP返回的可用列。

Moreover, your initial approach would result (if TVM allows SP) to unnecessary inserts to @Result table variable, so it could cause extra load on TEMPDB

此外,您的初始方法将导致(如果TVM允许SP)对@Result表变量进行不必要的插入,因此可能导致TEMPDB上的额外负载

#1