title: php and sql server stored procedure executing
body: <p>I have a stored procedure that do a login proccess and in different conditions return different value in same structure.</p>
  
  <p>this is my code:</p>
  
  <pre><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 &lt;= (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&gt; (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
  </code></pre>
  
  <p>the problem is that when execute this stored procedure like this :</p>
  
  <pre><code>$username = trim($username);
  $password = trim($password);
  $dbconn = new  db_connection();
  
  
  $select_statement = "execute I_SPCheckUserLogin '{$username}','{$password}'";
  echo $select_statement;
  $result = $dbconn-&gt;do_sql_command($select_statement);
  
  $count = 0;
  
  $row = $dbconn-&gt;fetch_array($result);
  
  
  if ($row['error'] == '0') {
  echo '&lt;br/&gt;';
  
      $this-&gt;load-&gt;library('session');
      $ci =&amp; get_instance();
      $data = array();
      $data['username'] = $username;
      $data['password'] = $password;
      $data['system_id'] = $system_id;
      $ci-&gt;session-&gt;set_userdata('user_info', $data);
      redirect('control_panel/index');
  } else
      echo $row['message'];
  </code></pre>
  
  <p>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</p>
  
  <p>sorry about my english</p>