How to use local variables in a SQL query used by the SqlDataSource component
|Product||Progress® Telerik® Reporting|
|Report Item||SqlDataSource Component|
In this article, we will explain how to use local variables in a SQL query used by the SqlDataSource component.
Sample SQL query:
Declare @localVariable varchar(max) Set @localVariable='SELECT * FROM MYTABLE' Exec(@localVariable)
- If the above is set as SQL text, the SqlDataSource Wizard will return an error like:
The variable name '@localVariable' has already been declared. Variable names must be unique within a query batch or stored procedure.
On configuring a SqlDataSource component you can map SQL parameters to report parameters - SqlDataSource Wizard step 4.
SQL queries relying on local variables can be turned into stored procedures.
The .NET provider will not consider the internally declared SQL parameters as procedure's parameters.
The syntax of the SQL query must be considered with the specification of the selected .NET data provider. It is the provider that interprets the SQL query and provides information 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.