How to use local variables in a SQL query used by the SqlDataSource component

RELATED TO:

AUTHOR: Stephania Tapela

DATE POSTED: December 01, 2016

Article information

Article relates to

Telerik Reporting

Created by

Stef, Telerik

Last modified

December 1st, 2016

Last modified by

Stef, Telerik

PROBLEM
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.


SOLUTION 

On configuring a SqlDataSource component you can map SQL parameters to report parameters - SqlDataSource Wizard step 4. For more details check Using Parameters with the SqlDataSource component.

Note that 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.

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.

Related Products
Telerik Reporting

Resources Buy Try

More Resources

  • Forums

    Find answers to thousands of questions or reach out to our active community

  • Demos

    Invoices, Sales Reports, Dashboards and other reports showcasing what you can accomplish with Telerik Reporting

  • Webinars

    Sign-up for upcoming webinars and watch recorded webinars

  • User Voice

    Get answers directly from the people who build Telerik controls

  • Documentation

    Detailed help articles and API references for Telerik Reporting

  • Knowledge Base

    Tutorials and working examples on how to achieve common scenarios

  • Code Library

    Sample projects submitted by Progress staff and community members

  • Blog

    Stay up-to-date with the latest functionality, how-to articles and industry trends

  • Release History

    Complete release history for Telerik Reporting.

  • System Requirements

    Operating system, .NET and browser support