This is a migrated thread and some comments may be shown as answers.

Using DECLARE Embedded SELECT Assumes Parameters

3 Answers 545 Views
Report Designer (standalone)
This is a migrated thread and some comments may be shown as answers.
Ben
Top achievements
Rank 1
Ben asked on 12 Jul 2016, 08:59 PM

I have a complex sql query i'm trying to embed into a report using the standalone designer.

It seems like everywhere I have DECLARE @aVariable the designer assumes I want that to be a parameter and then I get "The variable name '@aVariable' has already been declared...

How can I tell Telerik to allow my variables to be variables and not parameters?

Will I be forced to use a stored procedure?

 

Thanks!

3 Answers, 1 is accepted

Sort by
0
Stef
Telerik team
answered on 13 Jul 2016, 04:20 PM
Hi Ben,

the SqlDataSource component uses the information provided by the selected .NET data provider about the SQL query and its settings. If the .NET data provider recognizes the declaration as a report parameter, it will be listed in the SqlDataSource.Parameters.

Please test skipping the DECLARE part, and directly use the variables e.g. SQL query like:
--DECLARE @test int
select @test=5
select *,@test as [Test] from humanresources.department
When you are prompted to set values for the listed SQL parameters, add any values, since on executing the query the variables will take other values from the logic of the query.

The other approach is to use a stored procedure.


I hope this information is helpful.

Regards,
Stef
Telerik by Progress
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
Jenner
Top achievements
Rank 1
commented on 11 May 2021, 02:00 AM

Does not work with Table Variables
1
Ben
Top achievements
Rank 1
answered on 16 Jul 2016, 12:13 AM

I wanted to know how to make Telerik ignore variables in a query because it wants to use them as parameters but that is not what they are.
I moved to a stored procedure to solve this even though I don't think it should be necessary and you should have a way of telling Telerik not to use the variables as report parameters.

Thanks!

Jenner
Top achievements
Rank 1
commented on 12 Aug 2021, 12:51 AM

simple like that!
Still waiting.
0
Stef
Telerik team
answered on 20 Jul 2016, 08:57 AM
Hi Ben,

Please note that not all SQL databases support the DECLARE statement and it is a matter of .NET data provider implementation how SQL parameters are recognized.

Data providers provide information how SQL parameters to be recognized in quiries. We use this information to list SQL parameters and let the wizard map them to expressions/report parameters. If the data provider's information does not distinguish the declare statement and its SQL variable, the SqlDataSource Wizard does not include custom logic to do it instead.

The used workaround with a stored procedure is also our recommended approach. We will update the online documentation with further details about such scenarios and how the SqlDataSource Wizard works.

Regards,
Stef
Telerik by Progress
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
Jenner
Top achievements
Rank 1
commented on 11 May 2021, 02:09 AM

the year is 2021 and I still have the same problem. My script has table variables.
Our database is huge, and create a Stored Procedure as workarounds for each datasource is totally unacceptable.
Neli
Telerik team
commented on 20 May 2021, 02:18 PM

Hello Jenner,

Basically, if you are able to execute the script in the SQL Management Studio, then it should not be a problem to execute it in our designer. What we do is to get the text and forward it to the data adapter. You may also find helpful the Configuring Stored Procedure with Temporary Tables KB article.

Jenner
Top achievements
Rank 1
commented on 13 Jun 2021, 11:02 PM

Thanks, but you sent me an article about Temporary Tables. That is not I am talked about.
My Script has TABLE VARIABLES.
The first solution suggested in this post is "Skip the DECLARE". It does not work when it's about Table Variables.
Neli
Telerik team
commented on 24 Jun 2021, 06:54 AM

Hello Jenner,

We are investigating the case. Meanwhile, can you please send us the query with the table variable? Also, can you please confirm that you can execute it successfully in the SQL Management Studio?

Jenner
Top achievements
Rank 1
commented on 12 Aug 2021, 12:45 AM

Hi Neli, any news?

The query just try to use a SQL table type variable:

-- Telerik falls to use SQL Table Variable
-- This example convert a value list comma separeted on a Table Type Variable

declare @ParAgedRanges varchar(100) = '30, 60, 90'  -- Parameter (this line will be comment on Telerik)

declare @tAgedRanges table (days int) -- Table Variable to be joined on the main select 

insert into @tAgedRanges select days=convert(int,value) 
                           from string_split(@ParAgedRanges, ',') -- string_split returns a table with one Column named 'Value'

select * from @tAgedRanges

-- SQL Studio result is :

-- days
-- 30
-- 60
-- 90
Jenner
Top achievements
Rank 1
commented on 12 Aug 2021, 12:48 AM

using Telerik Report Designer v 14.0.20.219
Jenner
Top achievements
Rank 1
commented on 12 Aug 2021, 01:03 AM

Telerik Result is:
The Variable Name @tAgedRanges has already been declared...

(Even that I remove this variable from the Data Source Parameters)
Neli
Telerik team
commented on 16 Aug 2021, 11:19 AM

Hi Jenner,

Thank you for the update. Yes, I have news. I logged a bug-  SQLDatasource does not support SQL statement with Table Variables when connected to MSSQL database. It comes from the fact that we're using the settings of the connection itself to know how to determine the parameters in the SQL statement. The problem is that it recognizes as parameters every word that starts with "@" without checking the following word. In the Table Variables scenario, the following word after the parameter could be TABLE, meaning that the previous word that starts with @ must not be recognized as a parameter identifier.

I am afraid that currently, we are not able to provide a workaround.

As a token of gratitude, I updated your Telerik points. Please, let me know if you have any questions.

Jenner
Top achievements
Rank 1
commented on 16 Aug 2021, 11:51 PM

Thanks for your time Neli.

Once you are looking for a solution for Table Variables, maybe you can fix other issue.

The same problem you described above happens when using Cursors.
Any Script using CURSORS is not runs by Telerik.


As we know, each time we move thru the cursor using FETCH NEXT, we need to test the variable @@FETCH_STATUS for EOF.

So, also consider to not identify @@(anything) as parameters.
Neli
Telerik team
commented on 19 Aug 2021, 09:15 AM

Hi Jenner,

Have you tested the approach from the Configuring Stored Procedure with Temporary Tables KB article(solution for Cursor Variables:)?

Reem
Top achievements
Rank 1
commented on 13 Sep 2021, 02:13 PM

hello I have the same problem and I don't know what should I do now I tried the StoredProcedure and still have the same problem 
Neli
Telerik team
commented on 16 Sep 2021, 08:20 AM

Hi Reem,

You can test to execute the query in a sample console application. If it works correctly there but is not executed in the report designer, there might be a problem on our end. It will help me a lot if you can send me an example, so I can test it locally.

KungFuBrad
Top achievements
Rank 1
commented on 19 May 2022, 09:05 PM | edited

Had this same problem, though the solution was easy I didn't see it called out anywhere.

Basically, just make a Reporting parameter in the parent report of the same type that you need in your SQL statement and then just remove the DECLARE lines.

Granted, this won't work for Tables, Cursors and the like, but should for primitives.

Tags
Report Designer (standalone)
Asked by
Ben
Top achievements
Rank 1
Answers by
Stef
Telerik team
Ben
Top achievements
Rank 1
Share this question
or