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

[Aleksandr]

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

[Dimitar]

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