How to use variables inside a select (SQL Server)?

Unknown
Unknown
in Platform

Is it possibile to use variables inside a select?

For example I've tried this manual query in a datareader

 

DECLARE @anno as int
set @anno = 2018

select * from table where myyear=@anno

 

When I've tried to show the result, I received the error from the Boardserver:

Must declare the scalar variable "@anno".

 

It would be very helpful if we could use also variables, especially in complex sql-statements.

 

Regards,

Egon.

Answers

  • Helmut Heimann
    Helmut Heimann Employee, Community Captain
    50 Answers 500 Comments 250 Likes Fourth Anniversary
    edited March 2020

    Hi Egon Santoni,

     

    if it is just for time, you could use standard sql-functions in your where statement like this:

    image

    In any other case, I'd propose to populate a rolap in your sql database with your variables and then use this ROLAP in a sub-select to populate a view. Then use the SQL DataReader to get the desired data from the view. This might even work in a direct join between the SQL table and the ROLAP (although I have not tried that yet).

     

    Hope, this helps!

     

    Kind Regards,

    Helmut

Categories