none
Help in making a query driven by expression

    Domanda

  • I wrote the following query and it works very well

    declare @start date, @end date
    select @start = getdate()
    select @end = dateadd(day, -30, @start)


    ;with datesequence (date) as(
    select @start as Date
    union all
    select dateadd(day, -1, date)
    from datesequence
    where date > @end
    )


    select ProviderName, date, count(id) as ExceptionCount from InfraErrorLog
    inner join datesequence on date = dateadd(day, 0, datediff(day, 0, timecreated))
    group by ProviderName, date
    order by date desc

    but I need to dynamically change a few things in this and I try to convert it into an expression like

    ="

    declare @start date, @end date
    select @start = getdate()
    select @end = dateadd(day, -30, @start)


    ;with datesequence (date) as(
    select @start as Date
    union all
    select dateadd(day, -1, date)
    from datesequence
    where date > @end
    )


    select ProviderName, date, count(id) as ExceptionCount from InfraErrorLog
    inner join datesequence on date = dateadd(day, 0, datediff(day, 0, timecreated))
    group by ProviderName, date
    order by date desc"

    But it immediately gets and exception 

    Incorrect syntax near '@start'. Must declare the scalar variable "@start". Must declare the scalar variable "@start". Must declare scalar variable @end. Incorrect syntax near the keyword by

    How can i convert this into an expression?


    MSDNStudent Knows not much!

    venerdì 2 marzo 2012 06:39

Risposte



  • Hi MSDN Student,

    As far as I know, Reporting Services query designer doesn’t support the key word “declare”. That is why theerror occurs.

    And, I noticed thatthere is no report level parameter to use dynamic SQL. So, why not packed it as
    stored procedure and refer to the stored procedure in Reporting Services?

    Lola
    MSDN Community
    Support | Feedback to us

    Get or Request Code Sample from Microsoft

    Please remember to mark the replies as answers if they help and unmark them if
    they provide no help.



    Please remember to mark the replies as answers if they help.


    lunedì 5 marzo 2012 05:47
    Moderatore

Tutte le risposte