I am currently working on updating old scalar valued functions with the hope of making them more efficient or changing them into Inline TVFs.
While troubleshooting my rewrites I came across some oddities in the original code which do not seem to make sense based on my understanding of variables in SQL
If I were to write
Declare @var as Integer Select @var = col1 from table
Then this would always return the last item in col1. However, in the function that I am working with, this does not seem to be the case. Initially, there was a where clause which was changing results based on the input, but, removing that, the above property should hold. For the most part, it does, however there are a couple of cases where this is not true.
The outline is roughly similar to the following:
create function f ( @var1 ... ) returns datatype begin Declare @temp1 ... (several vars declared here) select @temp1 = col1, @temp2 = col2, ..., @tempX = max(colX) from( select col1, col2, ... , colX * (case when name = 'X' then 1 else 0) from table1 INNER JOIN table2 on ... inner join table3 on ... left join table4 on ... where x) as sq group by ... return ... end)
While more convoluted than the basic example above, with the where clause removed, it seems that it should be logically equivalent, however, If i run the function and look at what it's actually returning, it is not always the same value. Please note, that for testing purposes, the function is only returning one of the variables assigned through the select. Which, assuming similar functionality to the simple case given above, should always return the same value.
The values that are passed in as parameters should not affect this specific statement (this is a subset of the whole function, but I've created a smaller function to test this part of the code and decided to keep the same values passed in for consistency's sake). Only the newly declared variables are used and given values; excluding the where clause which I have commented out during my testing.
Then, my question is, how would these tables change in order to change the last element if the joins are always going to be the same and there is no where clause to change the values in the joins? grouping should be consistent as well since all of the tables should be the same every time the function is run.
The subquery should always return the same table and then the variables should be assigned the last value in their specific column, meaning that the return for the function should always be the same value.