Query in EXEC command with variable

Question

How can I get value in variable:

DECLARE @Query NVARCHAR(MAX)
DECLARE @CurrentModuleUsers INT

SET @Query = 'SELECT @CurrentModuleUsers = Count(UR.UserId)'+
            ' FROM      [dbo].[aspnet_UsersInRoles] AS UR '+
            ' INNER JOIN    [dbo].[aspnet_Roles] AS R ON UR.RoleId = R.RoleId '+
            ' INNER JOIN    [dbo].[aspnet_Users] AS U ON UR.UserId = U.UserId '+
            ' WHERE     LOWER(RoleName) IN          ( ' +
            '   SELECT  LOWER([Role]) '+
            '   FROM    ADMIN_ROLEACCESS '+         
            ')'

            EXEC(@query)
            print @CurrentModuleUsers

Error:

Must declare the scalar variable "@CurrentModuleUsers".

Please help me how to fix this.

Squirrel · Accepted Answer · 2016-08-30 05:24:04Z

3

use sp_executesql instead of exec()

exec sp_executesql @query, N'@CurrentModuleUsers INT OUTPUT', @CurrentModuleUsers OUTPUT

Why do you need to use dynamic sql here ? i don't see a need to do that

answered Aug 30, 2016 at 5:24

Squirrel

24.8k5 gold badges41 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

StackUser · Accepted Answer · 2016-08-30 07:43:15Z

0

There is no need of dynamic sql in your case. You can directly get the count using simple select.

DECLARE @CurrentModuleUsers INT

SELECT @CurrentModuleUsers = Count(UR.UserId)
FROM [dbo].[aspnet_UsersInRoles] AS UR
INNER JOIN [dbo].[aspnet_Roles] AS R ON UR.RoleId = R.RoleId
INNER JOIN [dbo].[aspnet_Users] AS U ON UR.UserId = U.UserId
WHERE LOWER(RoleName) IN (
        SELECT LOWER([Role])
        FROM ADMIN_ROLEACCESS
        )

PRINT @CurrentModuleUsers

answered Aug 30, 2016 at 7:43

StackUser

5,3983 gold badges29 silver badges48 bronze badges

1 Comment

Sami Over a year ago

True, but I am actually dealing with various different databases on the same server, database name is passed to procedure and have to build the dynamic query. Wondering if you share some idea for this.

Collectives™ on Stack Overflow

Query in EXEC command with variable

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related