sql - Must declare the scalar variable error for stored procedure -

i have following stored procedure:

create procedure validatelogin     (      @password varchar(200),      @username varchar(100),    @ipaddress   varchar(100)   )         begin        declare @qry varchar(max), @lockedip varchar(max), @lockedtime datetime, @timedifference int;         set @qry = 'select iduser, username, firstname, lastname, idorg, users.idrole, roles.title [role], allowed_ip users, roles users.idrole = roles.idrole      , lower(username) = @username , [password] = @password' ;         select         @lockedip = isnull(allowed_ip,''),        @lockedtime = isnull(lockedtime, getdate())     users     username = isnull(@username,'');         select         @timedifference = datediff(minute, @lockedtime, getdate())         if exists(select * users username = @username , password = @password , active = 1)        begin             if exists(select * users username = @username , isnull(islocked, 0) = 1)            begin -- be1               if(@lockedip = @ipaddress)                begin --be2                  if (@timedifference >5)                  begin --be5                      update users                   set islocked = 0, lockedtime = null                   username = isnull(@username,'')                     exec(@qry);                  end --be5               else                begin                  select 'your account has been locked.try after time' error               end            end --be2         else if(@lockedip!=@ipaddress)           begin --be4              update users          set islocked = 0, lockedtime = null          username = isnull(@username,'')            exec(@qry);          end --be4         end -- be1        else         begin --be3            exec(@qry);         end -- be3      end       end  go 

when execute through:

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

i following error:

msg 137, level 15, state 2, line 3
must declare scalar variable "@username".

i have declared variable in parameter list, error showing up.

please me.

how can resolve this?

exec() execute in different scope, parameters not found. should use sp_executesql , add parameters way:

declare @qry nvarchar(max);  set @qry = n'select iduser,username,firstname,lastname,idorg,users.idrole,roles.title [role],allowed_ip              users,roles              users.idrole=roles.idrole                 , lower(username)=@username              , [password]=@password' ;      execute sp_executesql @qry,                      n'@username varchar(100), @password varchar(200)',                      @username,                      @password;