MSSQL SuperSet Incorect Syntax near the Keyword 'DECLARE' when attempting to create chart with a valid query.

[huafist]

Bug description

SuperSet v4.1.1, MSSQL Server.
I have a query that starts with a DECLARE Statement:

DECLARE
@StartDate SMALLDATETIME,
@EndDate SMALLDATETIME

SELECT @StartDate = '20241201',
@EndDate = '20241231',

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
DROP TABLE #Temp

IF OBJECT_ID('temdb..#Temp2') IS NOT NULL
DROP TABLE #Temp2

....

In this order, the query runs in SQL Lab, but if I try to create a chart or save the dataset, I get the following error:
(156, b"Incorrect syntax near the keyword 'DECLARE'.DB-Lib error message 20018,....)

Alternatively, if I move the DECLARE and SELECT statements below "DROP TABLE #Temp2", the query again runs, but if I try to create a chart, I get the error:
Error parsing near 'OBJECT_ID' at line 4:12

Screenshots/recordings

No response

Superset version

4.1.1

Python version

3.9

Node version

16

Browser

Chrome

Additional context

No response

Checklist

• I have searched Superset docs and Slack and didn't find a solution to my problem.
• I have searched the GitHub issue tracker and didn't find a similar bug report.
• I have checked Superset's logs for errors and if I found a relevant Python stacktrace, I included it here as text in the "additional context" section.

[dosubot]

Hey @huafist! 👋 I'm here to help you with any bugs, questions, or contributions you have for Superset. I'm a bot designed to assist you while you wait for a human maintainer. Let's tackle this issue together!

The issue you're encountering with the DECLARE statement in MSSQL when creating charts in Superset might be related to how Superset processes SQL queries for chart creation. While I couldn't find specific documentation on handling DECLARE statements, there are some configurations and settings in Superset that might affect query execution.

1. SQL Parsing and Execution: Superset might handle SQL parsing differently when creating charts compared to running queries in SQL Lab. This could lead to syntax errors if certain SQL constructs are not supported in the chart creation context.

2. Configurations Affecting Query Execution: There are configurations like SQLLAB_TIMEOUT, SQLLAB_HARD_TIMEOUT, and SQL_MAX_ROW that can impact query execution, but these are more related to time limits and row constraints rather than syntax issues ^[1].

3. MSSQL Specific Configurations: Recent updates have introduced improvements for MSSQL, such as supporting CTE in virtual tables and respecting the datefirst setting. However, these changes might not directly address the DECLARE statement issue ^[2].

To work around this issue, consider restructuring your query to avoid using DECLARE statements at the beginning, or try using alternative methods to set variables, such as using subqueries or CTEs. Additionally, ensure that any temporary tables are handled correctly within the query context.

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Other}

[rusackas]

@sujiplr is the only person on our little rolodex who uses MSSQL... perhaps he can reproduce this or provide some input.

[huafist]

I suspect that the issue is tied to the limit that superstack puts on the result of the query set. I'm guessing that it is injecting the "TOP XXXXX" directive in a way that's confusing the parser.

[sfirke]

@huafist I think that's a good hypothesis. I wonder if you could confirm that by looking at a MSSQL profiler, would that show the inbound query that is failing?

[sujiplr]

@huafist , is there any specific reason why you are using the declare statement. As you mentioned the TOP command will cause issues, since you are using a declarative TSQL rather than a SQL statement. The requirement is looks like you are building certain intermediate dataset (as temp tables ) and wanted to use that in the final SQL statement for the chart data. This use case won't work with Superset Charts. You can make use of the CTE statements for this as an alternative.

[huafist]

@sujiplr the reason was I'm inexperienced with SuperSet and I was copying/pasting from T-SQL code I had already written. Through a little more research, I determined that SS doesn't support DECLARE or SET in its queries. For this particular instance, I used OPENQUERY to execute a stored procedure that generates the data that I need, but I have ran into another issue with getting date filters to populate initially - this is causing any other filters I may add (such as a "Region" filter, to error on execution. Should I create a separate thread for that issue?

[sujiplr]

@huafist , can you share the statement you are trying .

[huafist]

@sujiplr so as I mentioned, I'm using openquery to execute a stored procedure that returns the data that I need. The stored procedure takes 2 parameters - Start Date, End Date.
The SQL statement I'm using in the data source is:
select
Office,
RegionID,
DateEntered,
Open_Reqs,
Placed,
FillRatio
from openquery([LOOPBACK],
'exec [DBName].[dbo].[SPName]
@StartDate = '' {{ from_dttm }}'',
@EndDate = ''{{ to_dttm }}''
')

My end goal is a bar chart that I can add dashboard filters to that will let me change the date range, or filter the results by regionID. I added the following to the "Template Parameters" in the dataset's settings:
{
"_filters": [
{
"region": ["1", "4", "5", "6"]
}],
"from_dttm":"2024-11-04",
"to_dttm":"2024-11-10"
}

Formatted in this way, it lets me filter by region, but the start and end dates are hard coded and do not change.
If I move the variables around:
{
"_filters": [
{
"region": ["1", "4", "5", "6"],
"from_dttm":"2024-11-04",
"to_dttm":"2024-11-10"
}]
}
Then the date filters work, but I get an error on the Region filter that says "Error: (8114, b'Error converting data type varchar to smalldatetime.DB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\n')"
I'm assuming that it's complaining because there's no default value being passed for start date or end date, thus it can't get the region codes from the data set.
I would imagine this is a fairly trivial fix, but for the life of me, I have not been able to resolve it.