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

Functions are used for calculations, something like a SELECT query. You can't execute stored procedures inside a function. In this case you are trying to use the procedure sp_executesql inside the function and that is causing this issue.

If you wanted to perform the same steps, instead of functions you can use a procedure like below.

CREATE PROCEDURE dbo.GetUserModule
@UserDBName NVARCHAR(50),
@ModuleName NVARCHAR(150)

AS

BEGIN

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

END

Answer 2

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!