Can't use local variables in a SQL query used by the SqlDataSource component

1 Answer 79 Views
Report Designer (standalone)
Aleksandr
Top achievements
Rank 1
Aleksandr asked on 24 Nov 2021, 07:46 AM | edited on 24 Nov 2021, 07:47 AM

I have names. I want to set unique name as:  (name + ', ' + name + ', ' + ....) for text box value. This sql query work in my machine, but not work in telerik report designer, error message: 

The variable name '@myParameter' has already been declared. Variable names must be unique within a query batch or stored procedure.

 

DECLARE @myParameter VARCHAR(max)

SELECT @myParameter = COALESCE(@myParameter + ',', '') + g.[Name]
FROM [dbo].[NotificationEX] AS n
LEFT JOIN [dbo].[NotificationEXPurchaseMetods] AS m ON m.[ID] = n.[PurchaseMetod]
LEFT JOIN [dbo].[NotificationEXPurchaseMetodGroup] AS g ON g.[ID] = m.[Group]
WHERE [Placer] = 76875 AND [Status] IN (4, 14) AND [PublishedDate] >= GETDATE() - 365
GROUP BY g.[Name]

SELECT @myParameter 

 

This not help for my script: https://docs.telerik.com/reporting/knowledge-base/how-to-use-local-variables-in-a-sql-query-used-by-the-sqldatasource-component

1 Answer, 1 is accepted

Sort by
0
Dimitar
Telerik team
answered on 26 Nov 2021, 01:01 PM

Hello Aleksandr,

I noticed that a colleague of mine has already answered this question in a support ticket. For accessibility sake, I will also copy his answer in this public forum thread.

This error is expected as described in the How-to Implement Local Variables in a SQL Query article. The solution for this is to turn the SQL query into a Stored Procedure. Let me provide additional information and go over the solution below.

The .NET Provider interprets the SQL query and provides information about what ADO.NET classes to be used for retrieving data. If the .NET data provider recognizes the local variables as SQL parameters, they will be listed for mapping by the SqlDataSource Wizard, which will result in an error on executing the SQL query.

The recommended approach is to turn the SQL Query into a Stored Procedure. In this case, the .NET Provider will not consider the declared SQL Parameter as a procedure parameter. For additional reference, see the How-to Connect to a SQL Server Database Using Stored Procedure article.

Another option is to use a Calculated Field for the concatenated string. With this option, it may be possible to avoid using a SQL query with the local parameters altogether.

Please let us know if any further questions or issues arise.

Regards,
Dimitar
Progress Telerik

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/.
Tags
Report Designer (standalone)
Asked by
Aleksandr
Top achievements
Rank 1
Answers by
Dimitar
Telerik team
Share this question
or