I have a stored procedure that do a login proccess and in different conditions return different value in same structure.
this is my code:
create PROCEDURE [dbo].[I_SPCheckUserLogin]
@username nvarchar(50),
@password varchar(max)
AS
Begin
declare @incorrect_pass_msg nvarchar(MAX) = N'کاربر گرامی کلمه عبور نادرست است';
declare @login_success_pass_msg nvarchar(MAX) = N'کاربر گرامی عملیات ورود با موفقیت انجام شد';
declare @user_not_exists_msg nvarchar(MAX) = N'چنین کاربری یافت نشد';
declare @lock_user_msg nvarchar(MAX) = N'کاربر گرامی به دلایل امنیتی شما قادر به ورود نمی باشید';
declare @change_pass_msg nvarchar(MAX) = N'به دلایل امنیتی باید کلمه عبور تغییر یابد';
declare @error_number int = 0;
declare @msg nvarchar(100);
declare @version nvarchar(50);
declare @rule_lock_login_time int;
declare @rule_attempt_login int;
declare @rule_expire_days int;
select @version = ValueI from I_Rules where RuleName = 'Version';
select @rule_lock_login_time = ValueI from I_Rules where RuleName = 'LockLoginTime';
select @rule_attempt_login = ValueI from I_Rules where RuleName = 'AttemptToLogin';
select @rule_expire_days = ValueI from I_Rules where RuleName = 'PasswordExpireDays';
declare @md5_number int;
select top 1 @md5_number = Value from SplitString(@version,'.') order by Id desc;
declare @m_password nvarchar(MAX);
set @m_password = (select dbo.Md5Generator(@password , @md5_number));
declare @uid int ,
@uname nvarchar(max),
@upass nvarchar(max),
@ulast_login_date nvarchar(10),
@ulast_login_time nvarchar(10),
@ulock_login_time nvarchar(10),
@umust_change_pass bit,
@upass_never_expire bit,
@uattempt_login int,
@ulast_change_pass_date nvarchar(10);
select @uid = Id,
@uname = Username,
@upass = [Password],
@ulast_login_date = LastLoginDate,
@ulast_login_time = LastLoginTime,
@ulock_login_time = LockLoginTime,
@umust_change_pass = UserMustChangePassword,
@upass_never_expire = PasswordNeverExpire,
@uattempt_login = AttemptToLogin,
@ulast_change_pass_date = LastChangePasswordDate
from I_Users
where Username = @username and
IsActive='true';
declare @server_time nvarchar(10),
@server_date nvarchar(10);
select @server_date = [Date],
@server_time = [Time]
from dbo.GetShamsiDateTime();
if(@uname is null)
begin
set @error_number = 1;
set @msg = @user_not_exists_msg;
select @error_number error , @msg [message];
return;
end
else
begin
if(@ulock_login_time is not null)
begin
if( @server_time <= (select dbo.AddMinuteToTime(@ulock_login_time, @rule_lock_login_time)))
begin
set @error_number = 1;
set @msg = @lock_user_msg;
select @error_number error , @msg [message];
update I_Users Set LockLoginTime=@server_time Where Id=@uid;
return;
end
else
begin
update I_Users
set LastLoginDate = @server_date,
LastLoginTime = @server_time,
LockLoginTime = null,
AttemptToLogin = 0
where Id = @uid;
if(@@ERROR !=0)
begin
select 3 error , 'update faild' [message];
return;
end
end
end
if(@upass != @m_password)
begin
declare @tmp table(uattemp_login nvarchar(max));
update I_Users
set AttemptToLogin = AttemptToLogin + 1
output inserted.AttemptToLogin into @tmp
where Id = @uid;
if(@@ERROR !=0)
begin
select 3 error , 'update faild' [message];
return;
end
set @uattempt_login = (select uattemp_login from @tmp);
if @uattempt_login = @rule_attempt_login
begin
update I_Users set LockLoginTime = @server_time where Id=@uid
if(@@ERROR !=0)
begin
select 3 error , 'update faild' [message];
return;
end
end
set @error_number = 1;
set @msg = @incorrect_pass_msg;
select @error_number error , @msg [message];
return;
end
else
begin
if @umust_change_pass = 'true'
begin
set @error_number = 2;
set @msg = @change_pass_msg;
select @error_number error , @msg [message];
return;
end
else
begin
if @upass_never_expire = 'false'
begin
if @server_date> (select [date] from AddDaysToDate_Custom((select dbo.ShamsitoMiladi(@ulast_change_pass_date)),@rule_expire_days))
begin
set @error_number = 2;
set @msg = @change_pass_msg;
select @error_number error , @msg [message];
return;
end
end
update I_Users
set LastLoginDate = @server_date,
LastLoginTime = @server_time,
LockLoginTime = null,
AttemptToLogin = 0
where Id = @uid;
if(@@ERROR !=0)
begin
select 3 error , 'update faild' [message];
return;
end
set @error_number = 0;
set @msg = @login_success_pass_msg;
select @error_number error , @msg [message];
return;
end
end
end
end
the problem is that when execute this stored procedure like this :
$username = trim($username);
$password = trim($password);
$dbconn = new db_connection();
$select_statement = "execute I_SPCheckUserLogin '{$username}','{$password}'";
echo $select_statement;
$result = $dbconn->do_sql_command($select_statement);
$count = 0;
$row = $dbconn->fetch_array($result);
if ($row['error'] == '0') {
echo '<br/>';
$this->load->library('session');
$ci =& get_instance();
$data = array();
$data['username'] = $username;
$data['password'] = $password;
$data['system_id'] = $system_id;
$ci->session->set_userdata('user_info', $data);
redirect('control_panel/index');
} else
echo $row['message'];
the problem after a lot of test is that when executing the update statements in this stored procedure the rest of the stored procedure not executing from php but in the sql server management studio execute like charm
sorry about my english
