SQL Server - Set and use variable within select statement

Question

I have a SQL Server query with a calculation that I would like to call 'my_long_calculation' which I use in several places within my query. The problem is that I do not know how this 'my_long_calculation' calculation can be stored into a variable for easier readability.

So this is the calculation:

(FLOOR(DATEPART(DAY, DATEADD(MINUTE, [table1].someInt, [table1].someDate)) / 1440.0) % 2) + 1

What it does is not important to this question. What matters is that this calculation is long to write.

Now I use this same calculation in several places within the query like this (Don't try to understand it, just note that there are several places where I use the same calculation, but I don't use it in the SELECT statement, only in the JOIN and WHERE statements:

SELECT
    [table1].ID
FROM 
    [table1]
INNER JOIN 
    [table2] ON [table2].someattribute = (FLOOR(DATEPART(DAY, DATEADD(MINUTE, [table1].someInt, [table1].someDate)) / 1440.0) % 2) + 1
INNER JOIN 
   [table3] ON [User].someattribute = (FLOOR(DATEPART(DAY, DATEADD(MINUTE, [table1].someInt, [table1].someDate)) / 1440.0) % 2) + 1
WHERE 
    (FLOOR(DATEPART(DAY, DATEADD(MINUTE, [table1].someInt, [table1].someDate)) / 1440.0) % 2) + 1 = 1

Now I would like to write this monstrosity above like this (by replacing all instances of the same calculation by a shorter name):

--my_long_calculation = (FLOOR(DATEPART(DAY, DATEADD(MINUTE, [table1].someInt, [table1].someDate)) / 1440.0) % 2) + 1

SELECT
    [table1].ID
FROM 
    [table1]
INNER JOIN 
    [table2] ON [table2].someattribute = my_long_calculation
INNER JOIN 
    [table3] ON [User].someattribute = my_long_calculation
WHERE 
    my_long_calculation = 1

Those two queries are the same, I just replaced the long calculation for the name/variable 'my_long_calculation'.

The second statement, of course, throws an error because 'my_long_calculation' is not defined. But how can I define and set a variable within a query in order to improve readability?

IMPORTANT: I don't want to return the long calculation by including it in the select statement. I only want to use it on the join and where statements

uzi's suggestion is the correct one - one simple way would be to change your select to select t1.id from (select *, my_long_calculation = floor(...) from table1) as t1 join table2 on table2.someattribute = t1.my_long_calculation etc — ZLK
– ZLK, Commented Feb 14, 2018 at 4:03
Calculate your my_long_calculation in CTE (common table expression) and use in the main query. — Alex Kudryashev
– Alex Kudryashev, Commented Feb 14, 2018 at 4:39
What it does is not important to this question. No, it probably is important if you want an optimal solution. As written, your query will perform inefficiently since the optimizer cannot use any useful indexes to eliminate rows before the calculation. Perhaps a persisted computed column would help. — SMor
– SMor, Commented Feb 14, 2018 at 14:12

PSK · Accepted Answer · 2018-02-14 04:14:05Z

1

Best approach will be to crate a Scalar Valued function like following. By using function you don't have to write same formula everywhere. It is more maintainable.

CREATE function [dbo].[ufn_Calculate]
(
@someInt INT,
@someDate DATETIME
)
returns DECIMAL(15,2)

AS

Begin

DECLARE @CalculationOutput DECIMAL(15,2)
SELECT @CalculationOutput = (FLOOR(DATEPART(DAY, DATEADD(MINUTE, @someInt, @someDate)) / 1440.0) % 2) + 1
RETURN @CalculationOutput
End
GO

Now you can use it like following in your queries.

 SELECT
    [table1].ID
FROM 
    [table1]
    INNER JOIN [table2] ON [table2].someattribute = [dbo].[ufn_Calculate]([table1].someInt,[table1].someDate)
    INNER JOIN [table3] ON [User].someattribute = [dbo].[ufn_Calculate]([table1].someInt,[table1].someDate)
WHERE 
    [dbo].[ufn_Calculate]([table1].someInt,[table1].someDate) = 1

answered Feb 14, 2018 at 4:14

PSK

18k5 gold badges35 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Juan Castano Over a year ago

It is actually the same answer as @JohnWoo's. But this-one is easier to understand. Thanks

PSK Over a year ago

When I posted, answer was something else :). Later it was updated

John Woo · Accepted Answer · 2018-02-14 06:27:07Z

1

Based from your comment which you have deleted, you wanted to use it inside each iteration. In this case, you need to create a scalar function instead,

CREATE FUNCTION dbo.LongCalculation 
( 
   @firstParam INT,
   @secondParam DATETIME
) 
  RETURNS NUMERIC(10, 2)   -- specify correct data type here
  AS 
  BEGIN 
      --your logic here
  END 
GO

and use it inside your query,

SELECT
    [table1].ID
FROM 
    [table1]
    INNER JOIN [table2] ON [table2].someattribute =  dbo.LongCalculation([table1].someInt, [table1].someDate)
    INNER JOIN [table3] ON [User].someattribute =  dbo.LongCalculation([table1].someInt, [table1].someDate)
WHERE 
     dbo.LongCalculation([table1].someInt, [table1].someDate) = 1

edited Feb 14, 2018 at 6:27

answered Feb 14, 2018 at 3:53

John Woo

265k70 gold badges509 silver badges500 bronze badges

10 Comments

uzi Over a year ago

Your set clause will not work. Since it includes someInt column from [table1] table

John Woo Over a year ago

@uzi I know, that's what I stated in the next line.

ZLK Over a year ago

I don't think either of those queries will produce the desired result, actually. The first is incorrect syntax, the second just doesn't do what you might think it does.

John Woo Over a year ago

@JuanCastano then you are looking for a scalar function and not declaring and setting a variable.

John Woo Over a year ago

@ZLK the first one shows how to use DELCARE, the second one shows how to set the value inside a SELECT statement. of course this doesn't do what the user wants. there maybe filtering as well as other join.

|

Collectives™ on Stack Overflow

SQL Server - Set and use variable within select statement

2 Answers 2

2 Comments

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related