none
Passing Table Object from PowerQuery to SQL Server Stored Procedure RRS feed

  • Question

  • I am building a PowerView solution for a user that includes several PowerQueries that feed a PowerPivot data model.

    I have currently have a PowerQuery that is passing a scalar value to a SQL Server stored procedure. This works great.

    I now have a need to modify that PowerQuery to pass a table object into the same stored procedure. I have modified the stored proc to accept a Table valued parameter.

    The problem I am having is I can't figure out how to pass a table object from PowerQuery into the stored proc.

    Here's a snippet of my code:

    let
    // Parameter Setup
    
       // This references the ParameterQuery PowerQuery. This is the way to use one query result in
       // another.
    
       Parameter_Source = ParameterQuery,
    
    // Make sure we have a value to work with. An odd behavior of the M language is that
    // an expression is NOT EVALUATED if the result isn't used further down in the script.
    // Thus, here we are checking the Parameter_Source[Value]{0} from the ParameterQuery result
    // to see if it's null. If it is, we throw an error. If not, we assign that value to 
    // the ZipCode_Value variable. 
    
       ZipCode_Value  = if Parameter_Source[Value]{0} is null then
               error "No Zip code entered. Please check the Parameters tab."
           else
    	 Parameter_Source[Value]{0},
    
    
       // Grab the Radius value from the ParameterQuery results. This data is in the Value column
       // in row 1.	
       Radius = if Parameter_Source[Value]{1} is null then
                     0
                else
                     Parameter_Source[Value]{1},
    	
       // Call function which in turn will call the GeoSvc web service to return a table of 
       // Zipcodes within the search radius specified.
    
       ZipCodes = fnZipRadiusSearch(ZipCode_Value, Number.FromText(Radius)),
    
    // End Parameter Setup
    
        // Execute Stored Procedure in the POLK database to get the Polk Data.	
        Source = Sql.Database("MYSERVERNAME", "Polk", [Query="EXEC usp_GetPolkDataByZip " & ZipCodes ]),
    

    When I try to execute this, I get an error that says the "&" cannot be used with a Table object.

    Wednesday, January 7, 2015 8:51 PM

All replies

  • Hi Jeff,

    To clarify the problem, I want to confirm these things with you:

    1. Do you use Table-Valued Parameters in your stored procedure "usp_GetPolkDataByZip"?

    If you don't I think you need to go through this MSDN document for how to pass table parameter to the SQL Server from .Net application. This is actually not related to Office development, if you encounter some problems with how to use Table-Valued Parameters, please post your question in SQL Server forums and .Net Framework related forums.

    2. Your code is a part of VB.NET Excel Add-in project, right? Could you please give some detailed information about method "fnZipRadiusSearch"? What's the type of the variable "ZipCodes"?

    I recommend that you pass DataTable instance as the parameter to the stored procedure.

    a) Create a Type in the database, use this Type as the parameter of the stored procedure

    b) Convert the PowerQuery returned data to DataTable instance

    c) Pass the DataTable instance to the stored procedure

    You can find sample code in this MSDN document:

    http://msdn.microsoft.com/en-us/library/bb675163(v=vs.110).aspx


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, January 9, 2015 6:30 AM
    Moderator
  • Thanks for the reply. My code is written in the "M" language which is the language of PowerQuery. PowerQuery is a feature built into Excel 2013 Pro Plus (or is a COM addin for Excel 2013 Pro Plus, I can't remember). In either case, it is a capability developed by Microsoft.

    Monday, January 12, 2015 7:04 PM
  • Hi Jeff,  I was wondering if you every figured this out.  I'm trying to do something very similar but am stuck on the & error with table object.  
    Friday, November 13, 2015 3:43 PM
  • 先转换成json形式传递吧。

    肯定不能直接用查询表对象直接传递,数据库是不认的,,,

    Wednesday, January 22, 2020 5:03 AM