0

Suppose I have a situation where I have to solve a multi-variable equation that could be broken down in the following way.

Var1 = <some equation>
Var2 = <some equation involving Var1>
Var3 = <some equation involving Var1 and / or Var 2>
FinalVal = <some equation using any of the other variables>

In my SQL Server 2012 environment, I found that if I set up a statement to set the variables in the following way:

DECLARE
 @Var1
,@Var2
,@Var3
,@FinalVal
;

SELECT
     @Var1 = ...
    ,@Var2 = ...@Var1...
    ,@Var3 = ...@Var1, @Var2...
    ,@FinalVal = ...@Var1, @Var2, @Var3...
FROM
    MyTable

I could do it and get the correct values - Meaning @Var1 seems to be set first and the order of setting the variables continues down the line in the order of the select statement.

Is this something we can be sure to be the case - Meaning is it safe to set variables in this way? It has the advantage of only having to query the table once and all the logic is placed in a single statement rather than multiple SET statements. Or is this not a safe practice / should be avoided?

I know this may require some opinion on safety, but I'm moreso hoping there is a definitive answer of "yes, this is the way SQL Server will do it" or not that I can know the best way to move forward.

Thanks!

John Bustos
  • 19,036
  • 17
  • 89
  • 151

1 Answers1

1

yes, this is the way SQL Server will do it

https://www.mssqltips.com/sqlservertip/1888/when-to-use-set-vs-select-when-assigning-values-to-variables-in-sql-server/

DrHouseofSQL
  • 550
  • 5
  • 16
  • 1
    Thank you! - I can award this as a solution, but I believe the community may be upset for just this as an answer. I couldn't find any documentation to say this was the case. do you have any links to post just to show it true (or any way to verify it other than just experience)? - Just for the sake of making it a 100% complete answer. – John Bustos Oct 20 '17 at 20:33
  • @JohnBustos https://www.mssqltips.com/sqlservertip/1888/when-to-use-set-vs-select-when-assigning-values-to-variables-in-sql-server/ – DrHouseofSQL Oct 23 '17 at 12:07
  • I apologize, @DrHouseofSQL, I really want to assign this as a solution, but I can't see anywhere in that article where it states that this would occur. Am I just missing it... I'm sorry if I am and just being dense.... – John Bustos Oct 23 '17 at 15:12
  • I understand.... It is in the section for 'Assigning multiple values to multiple variables 'If you have to populate multiple variables, instead of using separate SET statements each time consider using SELECT for populating all variables in a single statement. This can be used for populating variables directly or by selecting values from database. ' Also in the conclusion section Using SELECT is efficient and flexible in the following few cases. •Multiple variables are being populated by assigning values directly I wont be put off if this is not what you are after – DrHouseofSQL Oct 23 '17 at 15:18
  • One more article I found, one of the comments under the answer indicates the order of the variables and their values" https://stackoverflow.com/questions/3945361/set-versus-select-when-assigning-variables – DrHouseofSQL Oct 23 '17 at 15:29