SQL Server execute (sp_executesql ) command in SQL function
<p>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:</p>

<blockquote>
  <p>Only functions and some extended stored procedures can be executed from within a function</p>
</blockquote>

<p>Function: (return number of users associated with @ModuleName)</p>

<pre><code>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
</code></pre>

<p>Query:</p>

<pre><code>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
</code></pre>

<p>Advice me if there is any better approach!</p>