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
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
The other approach is to use a stored procedure.
I hope this information is helpful.
Regards,
Stef
Telerik by Progress
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!
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
Our database is huge, and create a Stored Procedure as workarounds for each datasource is totally unacceptable.
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.
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.
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?
Hi Neli, any news?
The query just try to use a SQL table type 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
The Variable Name @tAgedRanges has already been declared...
(Even that I remove this variable from the Data Source Parameters)
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.
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.
Hi Jenner,
Have you tested the approach from the Configuring Stored Procedure with Temporary Tables KB article(solution for Cursor Variables:)?
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.
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.