当前位置: 动力学知识库 > 问答 > 编程问答 >

SQL Server execute (sp_executesql ) command in SQL function

问题描述:

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 '[email protected]+'.[dbo].[aspnet_UsersInRoles] AS UR '+

'INNER JOIN '[email protected]+'.[dbo].[aspnet_Roles] AS R ON UR.RoleId = R.RoleId '+

'INNER JOIN '[email protected]+'.[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!

网友答案:

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 isn't advisable at all on a longer run.

You can follow this question for a similar discussion!

Hope this helps in your decisions!

网友答案:

Basically function is used for the calculation purpose ,something like a select query. You can't execute a 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 the 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       '[email protected]+'.[dbo].[aspnet_UsersInRoles] AS UR '+
        'INNER JOIN '[email protected]+'.[dbo].[aspnet_Roles] AS R ON UR.RoleId = R.RoleId '+
        'INNER JOIN '[email protected]+'.[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
分享给朋友:
您可能感兴趣的文章:
随机阅读: