How to use variables within SELECT query in SQL Server

Question

I am migrating from MySQL to SQL Server. The following is a query that I had in MySQL, but I am not sure how to get variables working in SQL Server.

SET @listid = 0;

SELECT 
    list_id, last_updated, price, daily_return,
    @cumu_ret := ROUND(
        IF (@listid = list_id,
            IF(daily_return IS NULL, 1.0, @cumu_ret * (1 + daily_return)),
            IF(daily_return IS NULL, 1.0, last_cumulative_return * (1 + daily_return))), 10) AS cumulative_return,
    @listid := list_id AS set_id
FROM 
    daily_return 
ORDER BY 
    list_id, last_updated

SQL Server has a SUM() OVER function, but ideally I need PRODUCT() OVER.

Any ideas what I can do?

EDIT: when I try the following query...

 DECLARE @listid int = 0;
 DECLARE @cumu_ret decimal(24,10) = NULL;

SELECT 
    list_id, last_updated, price, daily_return,
    @cumu_ret = ROUND(
        IIF (@listid = list_id,
            IIF(daily_return IS NULL, 1.0, @cumu_ret * (1 + daily_return)),
            IIF(daily_return IS NULL, 1.0, last_cumulative_return * (1 + daily_return))), 10),
    @listid = list_id
FROM 
    #daily_return 
ORDER BY 
    list_id, last_updated

It throws an error:

A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

EDIT 2: the

A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations

error is a symptom and not my main problem. I cannot use any of the suggested workarounds.

I am trying to get a PRODUCT() OVER functionality going. Basically, in that column I am trying to get the previous cumulative return value and set the new row's value to previous cumulative * (1 + daily_return).

There is no `:=` in SQL Server, it's just `=`, and I don't think there is `PRODUCT() OVER` (as I know) — Ilyes, Mar 28 '19 at 17:39
@Sami Yes, but I had run into other problems and updated my question with more details — Shahid Thaika, Mar 28 '19 at 17:42
Just run two separate queries. You can't do both in SQL Server. Edit: Ok. I get what's going on. — alans, Mar 28 '19 at 17:43
http://www.sql-server-helper.com/error-messages/msg-141.aspx — Ilyes, Mar 28 '19 at 17:45
Possible duplicate of [A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations](https://stackoverflow.com/questions/4608140/a-select-statement-that-assigns-a-value-to-a-variable-must-not-be-combined-with) — Ilyes, Mar 28 '19 at 17:47
@Sami thanks for that link, but unfortunately I cannot use any of the suggested work arounds, cause I am trying to get a PRODUCT() OVER functionality going. Basically, in that column I am trying to get the previous cumulative return value and set the new row's value to previous cumulative * (1 + daily_return) — Shahid Thaika, Mar 28 '19 at 17:52
maybe you could try this exp(sum(log(column)))...supposedly works for positive numbers ...reference https://stackoverflow.com/questions/3912204/why-is-there-no-product-aggregate-function-in-sql...if not...you might need to use a cursor and build the data in a loop — Ctznkane525, Mar 28 '19 at 17:54
@Ctznkane525 Thanks, I'll check it out, but my data has negative numbers as well — Shahid Thaika, Mar 28 '19 at 18:00

score 1 · Answer 1 · answered Mar 28 '19 at 18:21

Your problem is that in SQL Sever, you need to set the variable outside the SELECT statement (as you have done correctly using DECLARE).

These lines are not allowed:

 @cumu_ret = ROUND(
        IIF (@listid = list_id,
            IIF(daily_return IS NULL, 1.0, @cumu_ret * (1 + daily_return)),
            IIF(daily_return IS NULL, 1.0, last_cumulative_return * (1 + daily_return))), 10),
    @listid = list_id

You want something more like this:

 DECLARE @listid int = 0;
 DECLARE @cumu_ret decimal(24,10) = NULL;
 SET @listid = list_id;
 SET @cumu_ret = select ROUND(
        IIF (@listid = list_id,
            IIF(daily_return IS NULL, 1.0, @cumu_ret * (1 + daily_return)),
            IIF(daily_return IS NULL, 1.0, last_cumulative_return * (1 + daily_return))), 10) as cumn_ret from #daily_return 

SELECT 
    list_id, last_updated, price, daily_return,
    @cumu_ret, @listid
FROM 
    #daily_return 
ORDER BY 
    list_id, last_updated

This will probably require some modification - I'm just trying to point you in the right direction... You might also want to look into doing a while loop for dynamic variables

David Browne - Microsoft · Accepted Answer · 2019-03-28T18:24:31.760

0

To implement a Product aggregate, remember your Algebra:

declare @t table (i int)
insert into @t(i) values (1),(2),(3),(4),(5)
select exp(sum(log(i))) product
from @t

outputs

product
----------------------
120

My data has negative numbers.

Then what we need to sprinkle on some modular arithmetic:

declare @t table (i int)
insert into @t(i) values (-1),(-2),(3),(-4),(5)
select exp(sum(log(abs(i)))) 
       * case when sum(case when sign(i) = -1 then 1 else 0 end) % 2 = 1 then -1 else 1 end product
from @t

outputs

product
----------------------
-120

edited Mar 28 '19 at 18:24

answered Mar 28 '19 at 18:03

David Browne - Microsoft

80,331
6
39
67

My data has negative numbers. – Shahid Thaika Mar 28 '19 at 18:10

score 0 · Answer 3 · answered Mar 28 '19 at 18:55

The following is what I have for now based on the answer by David Browne. It is a good starting point. In MySQL I was able to incrementally save my PRODUCT() OVER output, which I may not be able to do so in MS SQL, and may need to resort to cursors... let's see.

WITH daily_return AS (
    SELECT 0 AS ID, 0 AS daily_return UNION ALL
    SELECT 1 AS ID, 0.1 AS daily_return UNION ALL
    SELECT 2 AS ID, -0.2 AS daily_return UNION ALL
    SELECT 3 AS ID, 0 AS daily_return UNION ALL
    SELECT 4 AS ID, -1.1 AS daily_return UNION ALL
    SELECT 5 AS ID, 0.3 AS daily_return UNION ALL
    SELECT 6 AS ID, 0.2 AS daily_return
)
select id, daily_return,
    (exp(sum(log(abs(1+daily_return))) over (order by id)))
    * (case when (sum(case when sign(1+daily_return) = -1 then 1 else 0 end) over (order by id)) % 2 = 1 then -1 else 1 end) product
from daily_return

How to use variables within SELECT query in SQL Server

3 Answers3