Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
eferreira
New Member

Use SQL Store Procedure in Power BI

I have created a basic stored procedure without parameters in SQL Server, when I use the store procedure in Power Bi Desktop the return is ok in design but when I click on Save and Close in datasource edit the Power Bi gives me an errormessage like below, I'm trying to use this procedure as Direct Query.

 

Microsoft SQL: Incorrect syntax near the keyword 'EXECUTE'. Incorrect syntax near ')'.

 

I tried to change the statement but I got nothing.

 

The sql statement to execute the procedure is:

EXECUTE [Person].[SelectpersonByType]

 

I'm using the AdventureWorks database sample from Microsoft and my procedure statement is:

CREATE PROCEDURE [Person].[SelectpersonByType]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
select * from Person.Person
END

 

My customer is testing Power BI and are facing the same error, thanks in advanced.

42 REPLIES 42

I tried the same way to execute one of the stored procedure inside my Oracle package but failed

 

-- create a variable to hold SQL command to run
DECLARE @SqlCommand varchar(8000)
-- set the command to run
SET @SqlCommand = 'INTG_PA_BAL_DASHBOARD_PUB.populate_balance_db'
-- run it!
EXEC (@sqlCommand @parameter1=529,@parameter2="ALL",@parameter3="Feb-17",@parameter4=" ",@parameter=" ")

I tried the same way to execute one of the stored procedure inside my Oracle package but failed

 

-- create a variable to hold SQL command to run
DECLARE @SqlCommand varchar(8000)
-- set the command to run
SET @SqlCommand = 'INTG_PA_BAL_DASHBOARD_PUB.populate_balance_db'
-- run it!
EXEC (@sqlCommand @parameter1=529,@parameter2="ALL",@parameter3="Feb-17",@parameter4=" ",@parameter=" ")

@dsilveira Nice! Thanks for sharing.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

@Seth_C_Bauer I tried proposed solution but I am getting below error in Direct Query mode against Azure SQL data source.

 

Details: "EvaluateNativeQueryUnpermitted failure: the query 'DECLARE @sqlCommand varchar(1000)
SET @sqlCommand = 'dbo.uspGetSyncEventSchedulePB'
EXEC (@sqlCommand)
' isn't approved for execution."

 

I am not sure if I am missing something as datasource is Azure Sql.

@Vivs18  If you note, I make reference in my solution that it only works with Import. Not when using Direct Query.

If you want to continue to use DQ, could you create a View and reference that?


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

@Seth_C_Bauer I missed import in provided solution. But I tried with the proposed solution with Import but still it resulted in same error. I am not sure if I am missing anything here.

@Vivs18 It could be a limitation in Azure SQL DB... There are a bunch of differences - Reference


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

Small query over here,If we need to pass parameters to the procedure then how can we use this here?Could you share the syntax so that it would be helpful for beginners.

 

Thanks.

you can use below syntax:

 

EXEC IP_sel_OrderIntakeRS 'Mazlan','2020-11-04','2020-11-04'

Have you find any solution as I have similar issue as I wants to pass the input parameter
But it won't be allow

@vineetmodi@Zak2815@anakoom Letting the cat out of the bag a little early here as I'm going to write up a blog around this, but rather than use a stored procedure you can use a Table Valued Function. This function allows for input parameters to be passed in. For example: if I were to query the AdventureWorks2014 database and a function in there looks like this:

 

 

CREATE FUNCTION [dbo].[ufnGetContactInformation](@PersonID int)
RETURNS @retContactInformation TABLE 
(
    -- Columns returned by the function
    [PersonID] int NOT NULL, 
    [FirstName] [nvarchar](50) NULL, 
    [LastName] [nvarchar](50) NULL, 
	[JobTitle] [nvarchar](50) NULL,
    [BusinessEntityType] [nvarchar](50) NULL
)
AS 
-- Returns the first name, last name, job title and business entity type for the specified contact.
-- Since a contact can serve multiple roles, more than one row may be returned.
BEGIN
	IF @PersonID IS NOT NULL 
		BEGIN
		IF EXISTS(SELECT * FROM [HumanResources].[Employee] e 
					WHERE e.[BusinessEntityID] = @PersonID) 
			INSERT INTO @retContactInformation
				SELECT @PersonID, p.FirstName, p.LastName, e.[JobTitle], 'Employee'
				FROM [HumanResources].[Employee] AS e
					INNER JOIN [Person].[Person] p
					ON p.[BusinessEntityID] = e.[BusinessEntityID]
				WHERE e.[BusinessEntityID] = @PersonID;

		IF EXISTS(SELECT * FROM [Purchasing].[Vendor] AS v
					INNER JOIN [Person].[BusinessEntityContact] bec 
					ON bec.[BusinessEntityID] = v.[BusinessEntityID]
					WHERE bec.[PersonID] = @PersonID)
			INSERT INTO @retContactInformation
				SELECT @PersonID, p.FirstName, p.LastName, ct.[Name], 'Vendor Contact' 
				FROM [Purchasing].[Vendor] AS v
					INNER JOIN [Person].[BusinessEntityContact] bec 
					ON bec.[BusinessEntityID] = v.[BusinessEntityID]
					INNER JOIN [Person].ContactType ct
					ON ct.[ContactTypeID] = bec.[ContactTypeID]
					INNER JOIN [Person].[Person] p
					ON p.[BusinessEntityID] = bec.[PersonID]
				WHERE bec.[PersonID] = @PersonID;
		
		IF EXISTS(SELECT * FROM [Sales].[Store] AS s
					INNER JOIN [Person].[BusinessEntityContact] bec 
					ON bec.[BusinessEntityID] = s.[BusinessEntityID]
					WHERE bec.[PersonID] = @PersonID)
			INSERT INTO @retContactInformation
				SELECT @PersonID, p.FirstName, p.LastName, ct.[Name], 'Store Contact' 
				FROM [Sales].[Store] AS s
					INNER JOIN [Person].[BusinessEntityContact] bec 
					ON bec.[BusinessEntityID] = s.[BusinessEntityID]
					INNER JOIN [Person].ContactType ct
					ON ct.[ContactTypeID] = bec.[ContactTypeID]
					INNER JOIN [Person].[Person] p
					ON p.[BusinessEntityID] = bec.[PersonID]
				WHERE bec.[PersonID] = @PersonID;

		IF EXISTS(SELECT * FROM [Person].[Person] AS p
					INNER JOIN [Sales].[Customer] AS c
					ON c.[PersonID] = p.[BusinessEntityID]
					WHERE p.[BusinessEntityID] = @PersonID AND c.[StoreID] IS NULL) 
			INSERT INTO @retContactInformation
				SELECT @PersonID, p.FirstName, p.LastName, NULL, 'Consumer' 
				FROM [Person].[Person] AS p
					INNER JOIN [Sales].[Customer] AS c
					ON c.[PersonID] = p.[BusinessEntityID]
					WHERE p.[BusinessEntityID] = @PersonID AND c.[StoreID] IS NULL; 
		END

	RETURN;
END;

 

In my advanced options - SQL Query in Power BI I can call it just executing the select like this:

select * from [dbo].[ufnGetContactInformation]  (73)

and the output is returned as expected. This also allows you to do any CTE's or temp tables within the function.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

Is there another way to do this? In your example, you put the input parameters in the query. If I extract all the distinct values, in your case, all the PersonID as a table. And created a slicer, is there a way for the PBI used the value selected in the slicer and call the function to return the output?

 

Thanks!

Thanks but what if I wants to use SP instead of table valued function

@anakoom You can't. If you watch what is actually sent to the SQL server you will see that an added syntax is added to the command which invalidates the statement. In Import, it just executes the SQL statement. In Direct Query it changes it to something like Select * from () - this is off the top of my head. In either case, that is why the openquery works, or the table valued function because both of those wouldn't create un-executable code.

I'm giving you an alternative to a SP because that method doesn't work, you can transform whatever you are doing in that SP to the TVP. 


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

Thanks ..let me convert to TVF..is there any way I can allow user to
Pass parameter value and data will fetch based on given parameter?

@anakoom Probably not in the way you are thinking...

Power BI is best when you pull in the larger dataset and add slicers and filters to the report.

I'm not aware of any way to pass a parameter all the way downstream from a deployed report in the Power BI Service. You can do this in the desktop, but that would be stuck in that mode from my understanding.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

Hi,

table value function is just fine, but not work if you call it  with parameter which is not constant 😞

I tried to send any info about userid, dax expresion about userid, or anything similar which is not constant, didn't successed.

select * from TableFunction('abc') run fine, but

select * from TableFunction(USERPRINCIPALNAME()) fail...

 

Really It is awesome.It could help multiple users.As most of them not aware of this solution.If we might initimate in forum it would be great.

This solution seems to be working and its lot easier too.

 

Thanks much @Seth_C_Bauer!

Tango

@stangellapally 

1. tried to change from Direct query to import but the Power BI doesnt give me that option to change.

   It is most likely because you already have a connection to a SQL source in Direct Query mode. If that is the case, I believe this gets greyed out.

 

2. Also tried the OPENQUERY OPTION and I am now getting the below message:

 

Details: "Microsoft SQL: Server 'sv375002\lt1201' is not configured for DATA ACCESS."

 

You would need to enable direct access on your SQL Server. You can check what it is currently set at by running this:

select server_id, name, is_data_access_enabled from sys.servers

But I would definitely check with your DB team (if you aren't) before switching anything.

To enable you would execute EXEC sp_serveroption '<Your ServerName>', 'DATA ACCESS', TRUE

 

After enabling that feature you will be able to successfully execute the OpenQuery.

As noted, be sure you understand what you are enabling and what it is doing before executing anything.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors