I guess that I did something that should be a no-no... lol! I remember having seen this RETURN TABLE instead of a variable in a SQLCENTRAL article a few months ago, I was interested but honestly I didn't understand some crucial points and didn't push the exercise. Baby steps for me.
Steve I tried your code and I got
Msg 2010, Niveau 16, État 1, Procédure QtyOnHandNoExcl, Ligne 8 [Ligne de départ du lot 7]
Cannot perform alter on 'dbo.QtyOnHandNoExcl' because it is an incompatible object type.
If I get this right, your approach implies an union of the 3 tables, so 200000 rows instead of 40000-70000 and it still will work faster?
You would need to do a DROP and CREATE because you had a scalar function and this is an inline table-valued function.
The approach shows the UNION ALL among the 3 tables, but would only read one (or none) depending on the parameter as 2 of the queries will have conditions that return false without having to read the table.
Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?
How to post data/code on a forum to get the best help: Option 1 / Option 2