Using DECLARE Embedded SELECT Assumes Parameters

4 posts, 0 answers
  1. Ben
    Ben avatar
    5 posts
    Member since:
    May 2016

    Posted 12 Jul Link to this post

    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!

  2. Stef
    Admin
    Stef avatar
    3036 posts

    Posted 13 Jul Link to this post

    Hi Ben,

    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
    When you are prompted to set values for the listed SQL parameters, add any values, since on executing the query the variables will take other values from the logic of the query.

    The other approach is to use a stored procedure.


    I hope this information is helpful.

    Regards,
    Stef
    Telerik by Progress
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
  3. DevCraft banner
  4. Ben
    Ben avatar
    5 posts
    Member since:
    May 2016

    Posted 15 Jul in reply to Stef Link to this post

    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!

  5. Stef
    Admin
    Stef avatar
    3036 posts

    Posted 20 Jul Link to this post

    Hi Ben,

    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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
Back to Top