I'm implementing row-level-locking on a MS SQL Server. The function I created below works, but even with a very small data set, it takes a significant amount of time to return the results.
If I were to code this in VBA for example, I would call the username() function only once and store the result. Can this be done in a table-valued function?
How can I improve this function to perform faster?
CREATE Function [compliance].[fn_AllowActivity] (@ActivityID sysname) returns table with schemabinding as return select 1 as [fn_AllowActivity_result] from compliance.tbl_Users join compliance.tbl_Activity on compliance.tbl_Users.UnitID=compliance.tbl_Activity.UnitID where @ActivityID=compliance.tbl_Activity.ActivityID and compliance.tbl_Users.Active=1 and compliance.tbl_Users.Windows_ID=substring(user_name(),CHARINDEX('\',user_name())+1,10) or compliance.tbl_Users.Windows_ID = substring(user_name(),CHARINDEX('\',user_name())+1,10) and compliance.tbl_Users.Active=1 and compliance.tbl_Users.RoleID>=3 GO