My understanding is that sql server does not have parameterized views. Can a UDF be used in the same way as a View. Can a UDF be joined to other tables and be treated like a view. Can permissions be assigned to the UDF so that the user does not need permissions to the underlying tables. I appreciate any comments on this.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
User defined function to replace View
- Thread starter cmmrfrds
- Start date
Better forget it.
Primary purpose of UDF is to return single scalar value. Everything else spells "misuse" or "abuse".
UDF can return table, and this table can be JOINed with other tables. There are some limitations, overall syntax is redundant, such "views" are not updatable and except for few specific hacks performance is poor.
------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba
Primary purpose of UDF is to return single scalar value. Everything else spells "misuse" or "abuse".
UDF can return table, and this table can be JOINed with other tables. There are some limitations, overall syntax is redundant, such "views" are not updatable and except for few specific hacks performance is poor.
------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba
You could create an indexed view, and then use the WHERE Clause in the view to filter the results. This would give a high performance select on the VIEW.
Unfortunately the downside is a slight performance overhead in any amendments to the base tables, and also a few considerations and possible changes to procedures which use the view.
"I'm living so far beyond my income that we may almost be said to be living apart
Unfortunately the downside is a slight performance overhead in any amendments to the base tables, and also a few considerations and possible changes to procedures which use the view.
"I'm living so far beyond my income that we may almost be said to be living apart
- Thread starter
- #4
mrdenny
Programmer
- May 27, 2002
- 11,595
No, you can not put a parameter in the where clause within the view. To pass a procedure you would need to run a stored procedure. However with a procedure you can not join to it.
Denny
MCSA (2003) / MCDBA (SQL 2000)
--Anything is possible. All it takes is a little research. (Me)
![[noevil]](/data/assets/smilies/noevil.gif "[noevil] [noevil]")
(My very old site)
Denny
MCSA (2003) / MCDBA (SQL 2000)
--Anything is possible. All it takes is a little research. (Me)
(My very old site)
- Status
Similar threads
