必须为存储过程声明标量变量错误

I have following stored procedure:

我有以下存储过程：

CREATE procedure validateLogin    
(    
 @password varchar(200),    
 @username varchar(100),  
 @IpAddress   varchar(100)  
)    
AS    
BEGIN    
   Declare @qry varchar(max), @LockedIp varchar(max), @LockedTime DateTime, @TimeDifference int;    

   set @qry = 'select IdUser, UserName, FirstName, LastName, idOrg, Users.idRole, Roles.Title as [Role], Allowed_IP from Users, Roles where Users.idRole = Roles.idRole    
 and lower(UserName) = @username and [password] = @password' ;    

   select 
       @LockedIp = isnull(Allowed_IP,''),
       @LockedTime = isnull(LockedTime, getDate()) 
   from Users 
   where UserName = ISNULL(@username,'');    

   SELECT 
       @TimeDifference = DATEDIFF(MINUTE, @LockedTime, GETDATE())    

   IF exists(select * from Users where UserName = @username AND Password = @password AND Active = 1)    
   BEGIN     
       IF exists(select * from Users where UserName = @username AND isnull(IsLocked, 0) = 1)    
       BEGIN -- BE1    
          IF(@LockedIp = @IpAddress)     
          BEGIN --BE2    
             IF (@TimeDifference >5)    
             BEGIN --BE5    
                 UPDATE Users 
                 SET IsLocked = 0, LockedTime = null 
                 WHERE UserName = ISNULL(@username,'')  

                 exec(@qry);    
             END --BE5    
          ELSE     
          BEGIN    
             select 'Your Account has been Locked.Try after some time' as Error    
          END    
       END --BE2    
    Else IF(@LockedIp!=@IpAddress)     
     BEGIN --BE4    

        UPDATE Users 
        SET IsLocked = 0, LockedTime = null 
        WHERE UserName = isnull(@username,'')  

        exec(@qry);    
     END --BE4    
    END -- BE1    
   Else    
    BEGIN --BE3    
       exec(@qry);    
    END -- BE3    
 END      
END 
Go

When I execute this through:

当我执行此操作时：

exec validateLogin '|161|217|4|51','admin','127.0.0.1' 

I get following error:

我收到以下错误：

Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@username".

消息137，级别15，状态2，行3必须声明标量变量“@username”。

I have declared this variable in my parameter list, then also error is showing up.

我在参数列表中声明了这个变量，然后也出现了错误。

Please help me.

请帮帮我。

How can I resolve this?

我该如何解决这个问题？

1 个解决方案

#1

EXEC() will execute in a different scope, so your parameters are not found. You should use sp_executesql and add your parameters that way:

EXEC（）将在不同的范围内执行，因此找不到您的参数。您应该使用sp_executesql并以这种方式添加您的参数：

DECLARE @qry NVARCHAR(MAX);

SET @qry = N'select IdUser,UserName,FirstName,LastName,idOrg,Users.idRole,Roles.Title as [Role],Allowed_IP 
            from Users,Roles 
            where Users.idRole=Roles.idRole    
            and lower(UserName)=@username 
            and [password]=@password' ;   


EXECUTE sp_executesql @qry, 
                    N'@username varchar(100), @password varchar(200)', 
                    @Username, 
                    @Password;