mssql stored procedure execution support in SQL Lab?

[SimonTulettIdeaco]

Make sure these boxes are checked before submitting your issue - thank you!

• I have checked the superset logs for python stacktraces and included it here as text if any
• I have reproduced the issue with at least the latest released version of superset
• I have checked the issue tracker for the same issue and I haven't found one similar

Superset version

0.17.3

Expected results

It would be good if it was possible to use SQLLab to create a slice generated from the execution of a stored procedure when working with MSSQL, via invocation with the exec keyword or with the call keyword in MySQL. Support for dynamically passing arguments would be required and perhaps cold be achieved using Jinja2 as Mode Analytics does with liquid.

https://help.modeanalytics.com/articles/add-parameters-to-reports/

Actual results

N/A

Steps to reproduce

N/A

[mistercrunch]

Is there a way to express wrap a sproc inside a select statement? As is SELECT * FROM (EXEC my_sproc);

[SimonTulettIdeaco]

Thanks for getting back to me will give this a try. Would this method be able to support the passing of arguments from a filter slice do you think?

[mistercrunch]

Well assuming that a sproc could be queried like a view, and wrapped in a subquery, yes, but that may not be possible.

[mistercrunch]

Notice: this issue has been closed because it has been inactive for 377 days. Feel free to comment and request for this issue to be reopened.

[aswathkk]

This issue is not resolved yet.
My use case is to use dynamic tables as data source. In such a case, I've written a stored procedure which will dynamically returns the data. But since Superset doesn't support stored procedures as a data source, I'm not able to visualize the data.

@mistercrunch Please reopen this one.

[SWoto]

@mistercrunch Please reopen this one. I'm also facing the same problem.

[villebro]

This sounds like a related feature to extended CTE support which I'm currently looking into. It would be most helpful if you could elaborate with an example to make sure the feature request is as unambiguous as possible.

[MarkGStacey]

Please reopen.

Ideally:

Proc is for instance, "GetReturn_Timeseries_ByFund", with parameter @fundid int (e.g. CREATE PROC dbo.GetReturn_Timeseries_ByFund (@fundid INT))

We would then like to be able to execute this proc and get back the data by query folding from the final dashboard.

So we'd pass a Fund Id of 253 or 11792 via the URL, and have the proc execute " EXEC dbo.GetReturn_Timeseries_ByFund @fundid = 253"

Hopefully this explains the use case

[junlincc]

closed #13577 and keep track of this issue in this thread

[MarkGStacey]

Does this perhaps fit into some of the native filter enhancements?

[nealf]

Another use case (DB2 and MSSQL) we have for calling stored procedures uses the {{ current_username() }} jinja template to get a list of items the current user should have access to, which involves some sophisticated logic within the database. Some of our stored procedures could potentially be rewritten as table-valued functions, but I think a good chunk of them probably can't.

I realize there is probably a lot of work to make this a reality, but wanted to just add an additional use case.

[PriyaGanji]

Working with superset-1.3.0

I come up with solution for this.... By default superset is framing one type of query format for query charts, so if we use store procedure in sql editor, application is changing this storeprocedure query into defualt query format,then it throws error. so we need to pass directly sp query . For that we need to add code in superset > connectors > sqla > models.py

**query_word = str(tbl).casefold()
query_word = query_word.split()
query_words_list = ['exec','call','execute']

    if len(query_word) >0:
        if query_word[0] in query_words_list:
            qry=tbl

return SqlaQuery(
extra_cache_keys=extra_cache_keys,
labels_expected=labels_expected,
sqla_query=qry,
prequeries=prequeries,
)**

It's working fine for me............ PFA

[Ananthagopi07]

I tried the above code in my superset-1.3.0.
It worked successfully. Thanks for you your response.

[Ananthagopi07]

Who had a need for store procedure once the above code, it's working successfully.

[rscarborough1996]

Here is a potential solution, though I haven't thoroughly tested it myself. I tried this with Superset 2.0.1

By enabling DML queries on your DB connection, you can run stored procedures in SQL Lab. When you try to explore it, it fails. This is caused by a syntax error, because Superset tries to wrap the EXEC call like so: "FROM ( EXEC... )".
Using SQL_QUERY_MUTATOR in superset_config,py, you can intercept queries and manipulate the SQL string into something suitable.

1. Add this code to your superset_config:

def SQL_QUERY_MUTATOR(sql: str, **kwargs) -> str:
    if re.search("precode-", sql):
        lines = re.findall("precode-(?:(?!precode-|SELECT).)*", sql, re.MULTILINE)
        for index, line in enumerate(lines):
            sql = sql.replace(line, "")
            lines[index] = line.replace("precode-", "")

        precode = "\n".join(lines)

        sql = "\n" + precode + sql
    return sql

2. Create a dataset in SQL Lab with a query like this:

EXEC mySproc

3. Explore, and then edit the dataset and change the query to something like this:

precode-Declare @StoredProcTable Table (
precode-Id int,
precode-Name varchar(64),
precode-Value varchar(255)
precode-)
precode-Insert @StoredProcTable EXEC mySproc
SELECT * FROM @StoredProcTable

Anything preceded by "precode-" will be extracted from the "FROM ( ... )" statement, and prepended onto the query, so that the final query will look something like this:

Declare @StoredProcTable Table (
Id int, 
Name varchar(64),
Value varchar(255)
) 
Insert @StoredProcTable EXEC mySproc 
SELECT TOP 10000 [Id] AS [Id],
           [Name] AS [Name],
           [Value] AS [Value]
FROM
  (SELECT * FROM @StoredProcTable) AS virtual_table;

I'm sure there are some improvements that could be made, but hopefully someone will find this proof of concept useful.

[MajorLau]

I found little convenient way to let superset run mssql stored procedure execution.

1. add linked server:
   EXEC sp_addlinkedserver @server='MyLinkedServer', @srvproduct='', @provider='SQLNCLI', @datasrc='127.0.0.1'
2. create a proc to generate the superset search sql:
   `
   create proc GetSupersetSearchSql
   @procname nvarchar(20),
   @temptablename nvarchar(200)
   as
   begin
   declare @dbname nvarchar(200)
   set @dbname= DB_NAME()
   declare @finalsql nvarchar(max)
   DECLARE @columnsql NVARCHAR(2000)
   SELECT
   @columnsql = COALESCE(@columnsql + ', ', ' (') +
   QUOTENAME(c.name) + ' ' +
   t.name +
   CASE
   WHEN t.name IN ('varchar', 'nvarchar', 'char', 'nchar') THEN '(' +
   CASE
   WHEN c.max_length = -1 THEN 'MAX'
   ELSE CAST(c.max_length AS NVARCHAR(10))
   END + ')'
   WHEN t.name IN ('decimal', 'numeric') THEN '(' + CAST(c.precision AS NVARCHAR(10)) + ', ' + CAST(c.scale AS NVARCHAR(10)) + ')'
   ELSE ''
   END
   FROM
   tempdb.sys.columns c
   JOIN
   tempdb.sys.tables tbl ON tbl.object_id = c.object_id
   JOIN
   tempdb.sys.types t ON t.user_type_id = c.user_type_id
   WHERE
   tbl.name LIKE @TempTableName + '%'
   ORDER BY
   c.column_id
   SET @columnsql = @columnsql + ')'
   set @finalsql ='
   SELECT * FROM OPENQUERY(MyLinkedServer, ''SET FMTONLY OFF; SET NOCOUNT ON; exec '+@dbname+'.dbo.ExecProc '''''+@dbname+''''','''''+@procname+''''','''''+@columnsql+'''''
   WITH RESULT SETS
   ('+@columnsql+')'')'

print @finalsql
end
`

3. create excute proc:
   create proc ExecProc @databasename nvarchar(200), @procname nvarchar(200), @createsql nvarchar(max) as begin declare @sql nvarchar(max) set @sql=' declare @result TABLE '+@createsql+' insert into @result exec '+@procname+' select * from @result ' exec sp_sqlexec @sql end

4. and then you need modify your proc and use a temp table to save the result and add these sql,here is example:
   `
   create proc TestProc
   as
   begin
   select *
   into #result
   from PackedOil po

select * from #result
exec GetSupersetSearchSql 'TestProc','#result'
end
`
5. and your no need to change your old proc just save your result into a temp table and add the sql exec GetSupersetSearchSql 'proc name ','temp table name',then you run the sql in sqlserver client and get the searchsql in message: