Today for one of my application, i was trying to create a schema bound function for performance optimization. Before we go into the detail you should check this link to know that how the query optimizer can use it to make better performance decisions. Alright, now the function i wanted to create is used to check that particular role id have access to menu link or not. Simple right! Here is the code of my function.
CREATE FUNCTION [dbo].[udf_IsAuthorisedForMenuLink] ( @RoleID TINYINT, @MenuID INT ) RETURNS BIT WITH SCHEMABINDING AS BEGIN DECLARE @IsAuthorised BIT = 0 IF EXISTS(SELECT 1 FROM tblRoleMenu WHERE RoleId = @RoleID AND MenuID = @MenuID) BEGIN SET @IsAuthorised = 1 END RETURN @IsAuthorised END
But when i try to create this function, i get the following error.
So why i got this error? What is the resolution of this error? Well, the resolution itself is in the error message. As it said – “Names must be in two-part format” i.e. [Schema].[ObjectName] – in our case its dbo.tblRoleMenu. Actually i forgot to add schema in the select statement in my above function code. Once i add it and press “F5” and boom, function created successfully without any error.
Did you guys ever face this type of issue in your application? Write in the comment. Hope you like this post.