Persistence of function parameters

Question

Persistence of function parameters

cmartel 20772

Ten Centuries

Points: 1161
More actions
March 27, 2018 at 12:29 pm

#336837

The following function returns two rows having the same parent:
CREATE FUNCTION Test(@ParentID CHAR(38))
RETURNS TABLE AS RETURN
SELECT 'AAA' AS ID, @ParentID AS ParentID
UNION ALL
SELECT 'BBB' AS ID, @ParentID AS ParentID
If I call it like:
DECLARE @X VARCHAR(38) = '{' + CONVERT(VARCHAR(36), NEWID()) + '}'
SELECT * FROM Test(@X)
Then it returns the expected result, but if I call it like:
SELECT * FROM Test('{' + CONVERT(VARCHAR(36), NEWID()) + '}')
Then each row gets a different parent id.
Is there a way for the last call to behave like the first call, where SQL Sever would evaluate the parameter only once?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 1

In the first example, you're just passing the current value of a variable to the TVF, so it'll of course be the same in each query that's UNIONed in the TVF.

In the second example, you're passing an expression to the function, and since it's going to be inlined (you're using an iTVF), that expression will be evaluated for each row. NEWID() is part of that expression, and the whole point of NEWID() is that you get new values each time the function is evaluated, so you get different values for each row.

What is the issue with doing things as you do in the first example? It's by far the simplest way to get the results you want.

Cheers!

cmartel 20772 Ten Centuries Points: 1161 More actions · Answer 2

Thanks Jacob,

I would rather not declare a variable because the function that calls «test» is table-valued, itself called by another table-valued function. Turning the calling function into a «standard» function has an impact on performance.

Moreover, from a «pure» programming standpoint, I intuitively assume that an argument passed to a function is evaluated at function calling time and does not change value after the call. Obviously, if I write «SELECT NEWID() FROM Address», I expect a different ID for each address. This is not the case here.

I would have liked SQL Server to have an «immediate evaluation» function that would have allowed me to write «SELECT * FROM TEST(ImmediateEval(NEWID()))». Since this is not the case, I will slightly modify my functions so they do not need an ID to be passed as parameter.

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 3

Nesting functions, even inline functions, leads to all sorts of performance problems.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning