在SQL函数中执行SQL Server execute (sp_executesql )命令

Question

我在SQL Server中创建了一个函数，用于执行动态SQL查询并返回一个值。当我调用函数时，出现以下错误：

只有函数和一些扩展存储过程可以从函数内部执行

函数：（返回与@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

查询：

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

请告诉我是否有更好的方法！

- Sami

---

看一下这个链接(https://dev59.com/4WUp5IYBdhLWcg3wmIQs)，可能会有帮助 :) - Saurabh vijan

Answer 1

函数用于计算，类似于SELECT查询。您不能在函数内执行存储过程。在这种情况下，您正在尝试在函数内使用过程sp_executesql，这就导致了此问题。

如果您想执行相同的步骤，而不是使用函数，您可以使用以下过程。

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

- Unnikrishnan R

Answer 2

根据定义，函数不允许修改表内容。在这里，仅仅是SELECT语句，我理解但是在我看来函数并不是为此而设计的。存储过程才是实现这个技巧的最佳选择。

如果您仍想继续您想要做的事情，那么您可能需要使用一些技巧，如这个网站中提到的，但从长远来看这些都是不可取的。

您可以参考这个问题进行类似的讨论！

希望对您有所帮助！

- N00b Pr0grammer