SQLDatasource does not support SQL statement with Table Variables when connected to MSSQL database

Progress® Telerik® Reporting Feedback Portal

Create an account Log In

Back to Feed

Request a Feature Report a Bug

Unplanned

Last Updated: 26 Oct 2022 11:09 by ADMIN

Jenner

Created on: 16 Aug 2021 11:15

Type: Bug Report

SQLDatasource does not support SQL statement with Table Variables when connected to MSSQL database

The SQL datasource incorrectly recognizes Table Variables in its SQL statement as parameters. The SQL wizard shows the "Configure data source parameter" page and fails to execute the statement.

PROBLEM DESCRIPTION:
A simple statement that uses Table Variables is shown below:
USE AdventureWorks
DECLARE @TestUDTs TABLE(AddressTypeID int NOT NULL, [Name] dbo.Name NOT NULL PRIMARY KEY CLUSTERED(AddressTypeID))
INSERT @TestUDTs(AddressTypeID, [Name]) SELECT AddressTypeID,[Name]
FROM Person.AddressType SELECT * FROM @TestUDTs

4 comments

ADMIN

Milen | Product Manager @DX

Posted on: 26 Oct 2022 11:09

Hello Alberto,

Thank you for elaborating on the issue, the behavior, and what you need. I reproduced the issue and have two things to share:

1. The parameter tokens in the SQL text are parsed using pattern matching. When available, we read the pattern from the utilized data provider, e.g., the System.Data.SqlClient data provider. These patterns do not differentiate if it is a declared variable in your SQL batch of statements or an undeclared parameter that is expected when executing the command. We cannot change that behavior. Yes, we can try implementing a more sophisticated SQL parser, but given the fact we want to support any data provider, it would be complex and error-prone, which makes it a no-go in my opinion.

2. This pattern matching and parameter discovery should only happen when configuring the SqlDataSource and not at runtime. When processing the report, only the already defined data source parameters should be passed to execute the command. The current behavior is incorrect and I am rising the priority of this issue so that it can be addressed sooner.

What would be the result? When configuring the SqlDataSource using the dedicated wizard, the declared variables would still be inferred as parameters. But then you as a user that understands the semantics of the SQL batch will need to delete the incorrectly inferred parameters. This will allow the successful execution of the SQL batch.

Note that you would have two options to avoid the necessity of manually deleting incorrectly inferred parameters: Either using a single SELECT statement instead of an SQL batch, or if this does not suffice, encapsulate the SQL batch in a stored procedure and execute it instead.

Will the proposed change work for you? Any input is welcome.

Regards,
Milen
Product Manager for Telerik Reporting & Telerik Report Server @Progress

Brand new Telerik Reporting course in Virtual Classroom - the free self-paced technical training that gets you up to speed with Telerik and Kendo UI products. Check it out at https://learn.telerik.com/.

Julio

Posted on: 18 Oct 2022 16:47

Below is a t-sql sample query that Telerik Reporting should be able to deal with gracefully, requiring only @var1 parameter.

Although Telerik Reporting identifies 3 parameters (@var1, @var2 and @var3), as we can see, only @var1 should be considered a to be provided as required parameter, because its definition is missing. The other two should be provided, since their definition is already part of t-sql query batch. Worse, this happend even if we deleted all parsed parameters.

While this seems to be a slightly acceptable a design flaw a very-well known issue by the Telerik support team, often suggesting unacceptable workarounds like ad-hoc queries rewrite as stored procedures, it is now very hard to understand what as been preventing Telerik team from adding a parameter Ignore (disabled, by default) on [Configure data source parameters] form for so long. That would allow report designers to decide which of the poorly parsed parameters should be part of exec sp_executesql call parameters list.

By other words, I am wondering what is preventing this issue from being escalated to your product manager and valued as it deserves ?

Currently using 15.2.21.1125

/*
Sample t-sql query that Telerik Reporting should be able to deal with gracefully, requiring only @var1 parameter as an int.
*/

-- parameters begin here
-- declare @var1 int = 3
-- parameters end here

declare @var2 int = 123

declare @var3 as table(
col1 int identity(1,1),
col2 int)

insert into @var3 (col2)
select 400
union all select 200
union all select 100
union all select 300

select top (@var1) * from @var3 order by col2 asc

ADMIN

Neli

Posted on: 24 Aug 2021 10:15

Hi Tommy,

You may review the Configuring Stored Procedure with Temporary Tables KB article. If this does not work for you, can you please send us an example query that can be executed in a sample console application but it does not work in the report designer?

Regards,
Neli
Progress Telerik

Tommy

Posted on: 17 Aug 2021 21:43

I don't think it even support single variables... In our case when normally have to used a stored procedure for the SQLDataSource.