Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Passing PowerBI Parameter To A Stored Procedure

Hello everyone,

 

I am trying to create parameters within PowerBI so that I can pass the values to the parameters of a stored procedure.

This is the code for the procedure, which uses the AdventureWorksDW2019 database:

 

CREATE OR ALTER PROCEDURE [dbo].[TestProcWithParameters]
@CustomerKey INT
, @GeographyKey INT
AS
BEGIN

SELECT
[DC].[CustomerKey]
, [DC].[FirstName] + [DC].[LastName] AS [CustomerName]
, [DC].[GeographyKey]
, [DG].[EnglishCountryRegionName]
FROM [dbo].[DimCustomer] [DC]
INNER JOIN [dbo].[DimGeography] [DG]
ON [DC].[GeographyKey] = [DG].[GeographyKey]
WHERE
[DC].[CustomerKey] = @CustomerKey
AND [DC].[GeographyKey] = @GeographyKey;

END;

 

I'm attaching screenshots of the parameters to this post.

 

I followed the advice of this post, but I get an error:

https://community.powerbi.com/t5/Desktop/Execute-SQL-Server-stored-procedure-input-parameters-with-power/m-p/640915#M306942

 

let
Source = Sql.Database("localhost", "AdventureWorksDW2019", [Query="EXECUTE TestProcWithParameters " & CustomerKey & ", " & GeographyKey])
in
Source

Expression.Error: We cannot apply operator & to types Text and Number.
Details:
Operator=&
Left=EXECUTE TestProcWithParameters
Right=1

 

 

  • The error tells you exactly what the issue is.

     

    If you want IDs as part of your query string, they need to be in text format. So you have two options

    1. Define the parameters as text-valued rather than numbers.
    2. Convert the number to text in your query.

     

    "EXECUTE TestProcWithParameters " & Text.From(CustomerKey) & ", " & Text.From(GeographyKey)​

     

4 Replies

  • The error tells you exactly what the issue is.

     

    If you want IDs as part of your query string, they need to be in text format. So you have two options

    1. Define the parameters as text-valued rather than numbers.
    2. Convert the number to text in your query.

     

    "EXECUTE TestProcWithParameters " & Text.From(CustomerKey) & ", " & Text.From(GeographyKey)​

     

    • Anonymous's avatar
      Anonymous
      Not applicable

      Thanks. I actually didn't need to change the parameters. Just adding "Text.From" got it to work. I'll see if I need to change them as I build the visuals.

      • AlexisOlson's avatar
        AlexisOlson
        Super User

        Sorry if I wasn't clear. I gave two options that you could pick from. You don't need to do both. Just go with whatever makes more sense in your use case.