在同一存储过程中选择和更新，以及如何知道存储过程是否成功执行。

本文介绍了在同一存储过程中选择和更新，以及如何知道存储过程是否成功执行。的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 在使用用户登录面板时，我遇到了一种情况，我必须在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 设置 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， 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]值。 用户名=（字符串）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) outASBEGINSET NOCOUNT ON; SELECT COUNT(*) FROM TblAppUser Where Email = @AppUserEmail and UserPassword = @AppUserPasswordSET @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 = @AppUserPasswordENDtry { 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 outASBEGINSET NOCOUNT ON; --check record exist before updateIF 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 ENDELSE -- no record found Set @userValid = 0END 这篇关于在同一存储过程中选择和更新，以及如何知道存储过程是否成功执行。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！