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