SQL Server execute (sp_executesql ) command in SQL function

Question

I created a function in SQL Server to execute a dynamic SQL query and return a value. I'm getting error when calling function in SQL query:

Only functions and some extended stored procedures can be executed from within a function

Function: (return number of users associated with @ModuleName)

DECLARE @Query NVARCHAR(MAX)
DECLARE @Result int
DECLARE @UsersUsingModule AS NVARCHAR(99)

SET @Query = 'SELECT @UsersUsingModule = Count(UR.UserId) '+
        'FROM       '+@UserDBName+'.[dbo].[aspnet_UsersInRoles] AS UR '+
        'INNER JOIN '+@UserDBName+'.[dbo].[aspnet_Roles] AS R ON UR.RoleId = R.RoleId '+
        'INNER JOIN '+@UserDBName+'.[dbo].[aspnet_Users] AS U ON UR.UserId = U.UserId '+
        'WHERE      LOWER(RoleName) IN ( '+
            'SELECT LOWER([Role]) '+
            'FROM   ADMIN_ROLEACCESS '+
            'WHERE  LOWER(ModuleName) = LOWER(@ModuleName) '+
        ')'

        EXEC sp_executesql @Query, N'@ModuleName nvarchar(max), @UsersUsingModule INT OUTPUT', @ModuleName, @UsersUsingModule OUTPUT

SELECT @Result =  CAST(@UsersUsingModule as INT)
RETURN @Result

Query:

SELECT      
    M.ID as [ModuleID], M.ModuleName, CC.UserLicenses,
    dbo.fncRolesWithModule(M.ModuleName) AS [Roles],
    [dbo].[fncUsersUsingModule](M.ModuleName, 'USERS_Demo2016')
FROM        
    ADMIN_ClientsContracts as CC
INNER JOIN  
    ADMIN_Modules as M ON CC.ModuleID = M.ID
WHERE
    CC.Isactive = 1

Advice me if there is any better approach!

Answer 1

By definition, a FUNCTION is never allowed to ALTER table contents. Here in this it is just a SELECT, I understand but IMHO FUNCTION's aren't designed for that. STORED PROCEDURES are designed to do that trick.

If you still want to proceed with doing what you intend to do, then you might have to use some hacks as mentioned in this site, which aren't advisable at all on a longer run.

You can follow this question for a similar discussion!

Hope this helps!