Can't believe it.
Turn out the SQL2000 solution is not that straight forward.
Or, should I say, is very limited.
If we change the function a little bit:
CREATE FUNCTION dbo.TimesN
(@SomeINT int, @Multiplier int)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN SELECT Doubled = @SomeINT * @Multiplier
GO
this still works fine:
SELECT * FROM dbo.TimesN(10, 2)
Returns 20 as expected.
But if you try to use it against a table :
DECLARE @Result INT
--===== Begin measuring duration using GETDATE
DECLARE @StartTime DATETIME;
SELECT @StartTime = GETDATE()
--===== Test the code using the "throw-away" variable
SELECT @Result = (SELECT Doubled FROM dbo.TimesN(SomeNumber, 2))
FROM #TestTable
--===== "Stop the timer" and report the duration
PRINT DATEDIFF(ms,@StartTime,GETDATE())
You are getting this:
Msg 155, Level 15, State 1, Line 7
'SomeNumber' is not a recognized OPTIMIZER LOCK HINTS option.
Does not like 2 or more parameters, and that's it.
Same code works perfectly in SQL2008.
_____________
Code for TallyGenerator