Question

How to execute SQL Server stored procedure from Looker

  • 25 February 2020
  • 11 replies
  • 780 views

A complex query works in Looker SQL Runner but I prefer executing a stored procedure. When I entered exec storedprocedurename I got “The EXECUTE permission was denied on the object storedprocedurename, databasename, schema”. Please advise how to exec a stored procedure from Looker.


Thanks.


11 replies

Userlevel 4

You need to connect as some sort of DBA user, then execute something like:


grant execute on <stored_procedure> to looker_user


For details see: https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-object-permissions-transact-sql?view=sql-server-ver15

Thanks. I can execute stored procedure from SSMS. The link is showing how to grant execute access on SQL Server. I need to execute stored procedure from Looker.

I found a work around and decided to share. I created a SQL Server view that select data from a Table-value function. The function does what stored procedure does. In Looker, i select the view.

Userlevel 2

Hey @danyeungw


I think you can also create a Derived Table that accepts Looker Parameters that passes them in as the appropriate arguments to your stored procedure.

I don’t know how to execute stored procedure from Looker.

Userlevel 3

I think this could be a workaround

https://docs.looker.com/reference/view-params/create_process

Hi Cole,

Have you created a derived table that accepts Looker Parameters that passes them in as appropriate arguments to the stored procedures? if so, can you share your example please?

Userlevel 2

Hi @Srinivasan_Natarajan,


Sure!


Let’s say you had some function or stored procedure, your_function_or_stored_procedure, and it accepts 2 dates as arguments, a begin_date and an end_date.


It returns a table/result set in this case with columns of id and status.


The view that would accept Looker Parameters and then pass them appropriately to the function/stored procedure would look something like this:


view: your_view {
derived_table: {
sql:
select *
from your_function_or_stored_procedure(({% parameter begin_date %})::date, ({% parameter end_date %})::date)
;;
}

parameter: begin_date {
type: date
}

parameter: end_date {
type: date
}

dimension: id {
type: number
sql: ${TABLE}.id ;;
}

dimension: status {
type: string
sql: ${TABLE}.status ;;
}

measure: count {
type: count_distinct
sql: ${id} ;;
}
}

I hope this is helpful!

Thanks for the resposne and the example Cole. I am trying to implement this and will let you know .

The challange I have is that a Stored procedure cannot be called from within a select statement in SQL server. obviously I get an error "Invalid Object " Any alternative options for resolving this?

Userlevel 2

Ah, I see. We’re using Postgres, so a bit of a difference there. Sorry about that.


From some quick searching, you could look at creating a table variable that you then insert the results from executing your stored procedure into? You could then (in theory) select from that table variable in your derived table SQL?


I’m not at all confident that’s the right/best way to do it, though, and would definitely recommend you run that by someone at Looker Support who has more knowledge specifically about working with SQL Server in Looker.


Another option would be to create a table-valued function (I think someone mentioned that they did that in the thread above) and/or view in your SQL Server database that grabs the results from the execution of your stored procedure, and then returns them as a table. You could then select from that table-valued function and/or view in your Looker view. Potentially a lot of overhead and added maintenance that you take in with that kind of approach though.

Reply