How to use Stored procedure with parameter in Power bi

[AnanthanR]

I have SP with parameter in sql server. I tried to call (use) this in Power bi desktop but I am getting error. like incorrect syntax.

I am getting below error.

any help It would be helpfull for me.

Thanks,

Ananth

[TomMartens]

Hey @AnanthanR ,

hopefully, this video will get you started: https://www.youtube.com/watch?v=N1En6_NB_dY

Regards,

Tom

[AnanthanR]

Hi TomMartens,

Thanks for your time.

The same vedio i checked and followed the steps, but its not working getting error like, incorrect syntax.

Thanks,

Ananth

[TomMartens]

Hey @AnanthanR ,

without providing the exact source code it's difficult to spot anything unexpected.

Regards,

Tom

[AnanthanR]

Hi TomMartens,

I am getting error in first step itself. i entered server and DB details and i tried to use SP with Parameter.  getting Incorrect syntox error.

i ran below script in sql server. based on the suggestion provided in the vedio. but still not working.

EXEC
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC
sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE
GO

Thanks.

Ananthan

[TomMartens]

Hey @AnanthanR ,

having the error is "expected"

Make sure that you follow the steps from the video and edit the query inside Power Query after you have the closed the error dialog you shared initailly. The query needs to start with SELECT, this is the reason why you need the OPENROWSET.

Tom

[AnanthanR]

Thanks TomMartens,

I will change the query and try.

Thanks,

Ananth

[AnanthanR · Accepted solution]

Hi Tom,

After closing the error window, in Advanced editor i added the below code. Its working fine.

let
Source = Sql.Database("Servername", "AdventureWorksDW2019", [Query="SELECT * FROM OPENROWSET('SQLNCLI','server=Servername;trusted_connection=yes;','EXEC AdventureWorksDW2019.dbo.GetSalesDetails @Category=" & Category & "')" ])
in
Source

Thank you.

Ananth