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".
I have declared this variable in my parameter list, then also error is showing up.
Please help me.
How can I resolve this?
EXEC() will execute in a different scope, so your parameters are not found. You should use sp_executesql
and add your parameters that way:
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With