CODEWITHSUNDEEP
sqlsql-servert-sql
Sami
Sami

Reputation: 3800

Query in EXEC command with variable

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.

Upvotes: 0

Views: 36

Answers (2)

StackUser
StackUser

Reputation: 5398

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

Upvotes: 0

Squirrel
Squirrel

Reputation: 24763

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

Upvotes: 3

Related Questions