在同一存储过程中选择和更新，以及如何知道存储过程是否成功执行。 [英] Select and Update within the same Store Procedure, and How to know if store procedure is successfully executed.

问题描述

在使用用户登录面板时，我遇到了一种情况，我必须在sp中更新并选择语句，但我怎么知道这两个语句是否成功执行。

  CREATE   PROC  uspAppUserLogin 
  @ AppUserEmail   varchar （ 50 ）， @ AppUserPassword   varchar （max）， @ AppUserID   int  OUT， @ AppUsername   Varchar （ 25 ）OUT，
  @ LastLoginDate   varchar （ 20 ）out 
  AS  
  BEGIN  
  SET   NOCOUNT   ON ; 
  SELECT  COUNT（*） FROM  TblAppUser 其中电子邮件=  @ AppUserEmail  和 UserPassword =  @ AppUserPassword  
  SET   @ AppUserID  =（选择 UserId 从 TblAppUser 其中​​电子邮件=  @AppUserEmail  和 UserPassword =  @ AppUserPassword ）
  SET   @ AppUsername  =（选择 UserName 来自 TblAppUser 其中​​电子邮件=  @ AppUserEmail  和 UserPassword =  @ AppUserPassword ）
  SET   @ LastLoginDate  =（选择 ISNULL（LastLoginDate，GetDate（））来自 TblAppUser 其中电子邮件=  @ AppUserEmail  和 UserPassword =  @ AppUserPassword ）
 更新 TblAppUser < span class =code-keyword>设置 LastLoginDate = GetDate（）其中电子邮件=  @ AppUserEmail  和 UserPassword =  @ AppUserPassword  
  END  

 尝试 
 {
 db1.sqlcmd =  new  SqlCommand（  uspAppUserLogin）; 
 使用（SqlDataAdapter sda =  new  SqlDataAdapter（））
 {
 db1.sqlcmd.CommandType = CommandType.StoredProcedure; 
 db1.sqlcmd.Parameters.AddWithValue（  @ AppUserEmail，TxtBxEmail.Text.Trim （））; 
 db1.sqlcmd.Parameters.AddWithValue（  @ AppUserPassword，hashpassword）; 
 db1.sqlcmd.Parameters.Add（  @ AppUserID，SqlDbType.Int）; 
 db1.sqlcmd.Parameters.Add（  @ AppUsername，SqlDbType.VarChar，< span class =code-digit> 10 ）; 
 db1.sqlcmd.Parameters.Add（  @ LastLoginDate，SqlDbType.VarChar，< span class =code-digit> 20 ）; 
 db1.sqlcmd.Parameters [  @ AppUserID]。Direction = ParameterDirection.Output; 
 db1.sqlcmd.Parameters [  @ AppUsername]。Direction = ParameterDirection.Output; 
 db1.sqlcmd.Parameters [  @ LastLoginDate]。Direction = ParameterDirection.Output; 
 db1.sqlcmd.Connection = db1.sqlcon; 
 db1.sqlcon.Open（）; 
 db1.sqlcmd.ExecuteNonQuery（）; 
 Userid =（ int ）db1.sqlcmd.Parameters [  @ AppUserID]值。 
用户名=（字符串）db1.sqlcmd.Parameters [  @ AppUsername]值。 
 recentlogin =（ string ）db1.sqlcmd.Parameters [  @ LastLoginDate]值。 
} 
} 
  catch （Exception ex）{Response.Write（ex.Message）; } 
 最后 
 {
 如果（usercount ==  1 ） //  比较表中的用户 
 {
会话[  AppUserName] =用户名; 
会话[  AppUserID] = Userid; 
会话[  recentlogin] = recentlogin; 
 Response.Redirect（  HomeAppUser.aspx）;  //   for sucsseful login  
} 
  else  
 {
 db1.sqlcon.Close（）; 
 ClientScript.RegisterStartupScript（GetType（），  alert，  alert（' +  无效的登录详细信息 +  '）;，真）; 
 Response.Redirect（  CandidateLogin.aspx）; 
} 
} 

解决方案

你可以这样做

   -   添加新的参数 
  @ userValid   bit  out 
  AS  
  BEGIN  
  SET   NOCOUNT   ON ; 
   -   更新前检查记录 
  IF   EXISTS （ SELECT  *  FROM  TblAppUser 其中电子邮件=  @ AppUserEmail  和 UserPassword =  @ AppUserPassword ）
  BEGIN  
   -   记录存在 
  SET   @ AppUserID  =（选择 UserId 来自 TblAppUser 其中电子邮件=  @ AppUserEmail  和 UserPassword =  @ AppUserPassword ）
  SET   @ AppUsername  =（选择用户名来自 TblAppUser 其中电子邮件=  @ AppUserEmail  和 UserPassword =  @ AppUserPassword ）
  SET   @ LastLoginDate  =（选择 ISNULL（ LastLoginDate，GetDate（）） From  TblAppUser 其中 Email =  @ AppUserEmail  和 UserPassword =  @ AppUserPassword ）
 更新 TblAppUser 设置 LastLoginDate = GetDate（） 其中电子邮件=  @ AppUserEmail  和 UserPassword =  @ AppUserPassword  
 设置  @ userValid  =  1  
  END  
  ELSE  
   -   未找到记录 
 设置  @ userValid  =  0  
  END  

While Working on User Login Panel, I came across a situation where i have to update and select statement both in sp, but how would i know if these both statements are executed successfully.

CREATE PROC uspAppUserLogin
@AppUserEmail varchar(50),@AppUserPassword varchar(max),@AppUserID int OUT,@AppUsername Varchar(25) OUT,
@LastLoginDate varchar(20) out
AS
BEGIN
SET NOCOUNT ON; 
SELECT COUNT(*) FROM TblAppUser Where Email = @AppUserEmail and UserPassword =  @AppUserPassword
SET @AppUserID =(Select UserId  From TblAppUser Where Email = @AppUserEmail and UserPassword =  @AppUserPassword)
SET @AppUsername =(Select UserName From TblAppUser Where Email = @AppUserEmail and UserPassword =  @AppUserPassword)
SET @LastLoginDate = (Select ISNULL(LastLoginDate,GetDate()) From TblAppUser Where Email = @AppUserEmail and UserPassword =  @AppUserPassword)
Update TblAppUser Set LastLoginDate = GetDate() Where Email = @AppUserEmail and UserPassword =  @AppUserPassword
END

try
          {
              db1.sqlcmd = new SqlCommand("uspAppUserLogin");
              using (SqlDataAdapter sda = new SqlDataAdapter())
              {
                  db1.sqlcmd.CommandType = CommandType.StoredProcedure;
                  db1.sqlcmd.Parameters.AddWithValue("@AppUserEmail", TxtBxEmail.Text.Trim());
                  db1.sqlcmd.Parameters.AddWithValue("@AppUserPassword", hashpassword);
                  db1.sqlcmd.Parameters.Add("@AppUserID", SqlDbType.Int);
                  db1.sqlcmd.Parameters.Add("@AppUsername", SqlDbType.VarChar, 10);
                  db1.sqlcmd.Parameters.Add("@LastLoginDate", SqlDbType.VarChar, 20);
                  db1.sqlcmd.Parameters["@AppUserID"].Direction = ParameterDirection.Output;
                  db1.sqlcmd.Parameters["@AppUsername"].Direction = ParameterDirection.Output;
                  db1.sqlcmd.Parameters["@LastLoginDate"].Direction = ParameterDirection.Output;
                  db1.sqlcmd.Connection = db1.sqlcon;
                  db1.sqlcon.Open();
                  db1.sqlcmd.ExecuteNonQuery();
                  Userid = (int)db1.sqlcmd.Parameters["@AppUserID"].Value;
                  Username = (string)db1.sqlcmd.Parameters["@AppUsername"].Value;
                  recentlogin = (string)db1.sqlcmd.Parameters["@LastLoginDate"].Value;
              }
          }
          catch (Exception ex) { Response.Write(ex.Message); }
          finally
          {
              if (usercount == 1)  // comparing users from table 
              {
                  Session["AppUserName"] = Username;
                  Session["AppUserID"] = Userid;
                  Session["recentlogin"] = recentlogin;
                  Response.Redirect("HomeAppUser.aspx");  //for sucsseful login  
              }
              else
              {
                  db1.sqlcon.Close();
                  ClientScript.RegisterStartupScript(GetType(), "alert", "alert('" + "Invalid Login Details " + "');", true);
                  Response.Redirect("CandidateLogin.aspx");
              }
          }

解决方案

you can do as below

--added new out parameter 
@userValid bit out
AS
BEGIN
SET NOCOUNT ON; 
--check record exist before update
IF EXISTS(SELECT * FROM TblAppUser Where Email = @AppUserEmail and UserPassword =  @AppUserPassword)
BEGIN 
    --record exist 
    SET @AppUserID =(Select UserId  From TblAppUser Where Email = @AppUserEmail and UserPassword =  @AppUserPassword)
    SET @AppUsername =(Select UserName From TblAppUser Where Email = @AppUserEmail and UserPassword =  @AppUserPassword)
    SET @LastLoginDate = (Select ISNULL(LastLoginDate,GetDate()) From TblAppUser Where Email = @AppUserEmail and UserPassword =  @AppUserPassword)
    Update TblAppUser Set LastLoginDate = GetDate() Where Email = @AppUserEmail and UserPassword =  @AppUserPassword
    Set @userValid = 1 
END
ELSE
    -- no record found 
    Set @userValid = 0
END

这篇关于在同一存储过程中选择和更新，以及如何知道存储过程是否成功执行。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！