Forum Discussion
RAISERROR or THROW in Power Query Store Procedure for Custom Errors
I have around 4 Excel power query's that run a stored procedure in SQL Server. I'm using Azure SQL Database.
I need to be able to do a check in the stored procedure, and raise an error if certain logic is true (and display that error in Excel). System errors work just fine (We cannot convert the value a12455 to type Number).
It could be something as simple as...
IF @@ROWCOUNT = 0
BEGIN
RAISERROR(N'NO JOB WAS FOUND IN THE REPORT!', 18, 1);
END
In reality though, I will be checking for much more complex logic, and then returning an error if certain conditions are not met. This is not something I can do by handling the errors in Excel itself, which is why I want to raise the error from SQL.
Can anyone assist? It seems to just refresh the query and never pops up the error. Ideally, I would like a message box to pop up that shows the error from SQL Server.
I'm no Excel expert, so I figured I would start here.
8 Replies
If you want a complete refresh failure to occur if some condition isn't true, then you could add a step after the first step to the query like:
= if Table.RowCount(Source) = 0 then error "No rows in table!" else Source
Note if you add a column and put similar logic there, you will instead get a warning that some of the rows contain errors when you try and do a refresh.
Sorry, the example I posted was overly simplified. In actuality, I will query probably 3-4 tables, make sure a value isn't missing when it should be using a combination of 3-4 variables in the stored procedure. I didn't want to post anything overly complex like that because all I'm looking for is a mechanism to display that error to the Excel user from the SQL Database.
Like I said, it's a bit too complex to just implement on the Excel side. Thanks for your idea though!
Oh I think I get what your saying... you want to handle an error from SQL and give it to the user.
In that case it would be something like:
let
Result = (try <Your SQL sproc call>),
HasError = Result[HasError],
ErrorMessage = Result[Error][Message],
ErrorDetails = Result[Error][Detail],
ValueNoError = Result[Value]
in
if HasError then ErrorMessage else ValueNoError
You can also do more in depth info checking with ErrorDetails.
