请教这几个存储过程怎么写啊
存储过程名称及功能 输入参数 输出参数 返回值
CustomerLogin @email 无 >0:客户唯一ID
新客户注册 @password -1:登录失败
----------------------------------------------------------
EditCustomer @PKID 无 0:更新成功
修改客户信息 @email 1:用户不存在
@NEWpassword 2:密码不正确
@oldpassword
@name
------------------------------------------------------------
GETcustomers 无 无 0
-------------------------------------------------------------
getcustomerbyID @PKID 无 0
-------------------------------------------------------------
这里主要是对客户表进行操作的Customers表包含的字段有:PKID,email,password,name
深表感谢
存储过程名称及功能 输入参数 输出参数 返回值
CustomerLogin @email 无 >0:客户唯一ID
新客户注册 @password -1:登录失败
----------------------------------------------------------
EditCustomer @PKID 无 0:更新成功
修改客户信息 @email 1:用户不存在
@NEWpassword 2:密码不正确
@oldpassword
@name
------------------------------------------------------------
GETcustomers 无 无 0
-------------------------------------------------------------
getcustomerbyID @PKID 无 0
-------------------------------------------------------------
这里主要是对客户表进行操作的Customers表包含的字段有:PKID,email,password,name
深表感谢
-------------------------------------------------------------
GETcustomers 无 无 0
------------------------------------------------------------- Create procedure GETcustomers
as
begin
select * from customers
end
-------------------------------------------------------------
getcustomerbyID @PKID 无 0
------------------------------------------------------------- Create procedure GETcustomers
(@pKid int)
as
begin
select * from customers
where pkId=@pKId
end
存储过程名称及功能 输入参数 输出参数 返回值
CustomerLogin @email 无 > 0:客户唯一ID
新客户注册 @password -1:登录失败
----------------------------------------------------------
EditCustomer @PKID 无 0:更新成功
修改客户信息 @email 1:用户不存在
@NEWpassword 2:密码不正确
@oldpassword
@name
这里主要是对客户表进行操作的Customers表包含的字段有:PKID,email,password,name
深表感谢
------------------------------------------------------------
1、
create proc customerlogin(
@email varchar(50),
@password varchar(50))
as
begin
declare @i int
begin trans
insert customers select -1,@email,@password,''
if @@error begin rollback trans return -1 end
select @i = isnull(max(pkid),0)+1 from customers
update customers set pkid = @i where pkid = -1
if @@error begin rollback trans return -1 end
commit trans
return @i
end
go
create proc EditCustomer
(@pkid int,@email varchar(50),@newpassword varchar(50),@oldpassword varchar(50),@name varchar(50))
as
begin
if not exists (select 1 from customers where pkid = @pkid) return 1
if not exists (select 1 from customers where pkid = @pkid and password = @oldpassword) return 2
update customers set email=@email,name = @name,password = @password where pkid = @pkid
if @@error <> 0 return -1
return 0
end
--------------------------------------------------------------------------------由于目标机器积极拒绝,无法连接。 192.168.1.242:8086
说明: 执行当前 Web 请求期间,出现未处理的异常。请检查堆栈跟踪信息,以了解有关该错误以及代码中导致错误的出处的详细信息。 异常详细信息: System.Net.Sockets.SocketException: 由于目标机器积极拒绝,无法连接。 192.168.1.242:8086源错误: 执行当前 Web 请求期间生成了未处理的异常。可以使用下面的异常堆栈跟踪信息确定有关异常原因和发生位置的信息。 堆栈跟踪:
[SocketException (0x274d): 由于目标机器积极拒绝,无法连接。 192.168.1.242:8086]
System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg) +2668969
System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type) +717
CSDN.Community.TopicFileDataCenter.TopicFileComponent.Reply(ReplyGenerateData rgd) +0
CSDN.Community.TopicFileDataCenter.TopicFileDataCenterEntry.Reply(Guid topicId, DateTime createDate, ReplyInfo reply, CommunityUser user, UserSectionProfile usp) +72
CSDN.Community.PointForum.Services.ReplyTopicManager.ReplyTopic(ReplyInfo reply, DateTime topicPostDate, String& errorInfo, CommunityUser user, UserSectionProfile usp) +659
CSDN.Community.PointForum.WebControls.ReplyTopicPage.bt_Submit_Click(Object sender, EventArgs e) +602
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1746
--------------------------------------------------------------------------------
版本信息: Microsoft .NET Framework 版本:2.0.50727.1433; ASP.NET 版本:2.0.50727.1433
--------------------------------------------------------------------------------由于目标机器积极拒绝,无法连接。 192.168.1.242:8086
说明: 执行当前 Web 请求期间,出现未处理的异常。请检查堆栈跟踪信息,以了解有关该错误以及代码中导致错误的出处的详细信息。 异常详细信息: System.Net.Sockets.SocketException: 由于目标机器积极拒绝,无法连接。 192.168.1.242:8086源错误: 执行当前 Web 请求期间生成了未处理的异常。可以使用下面的异常堆栈跟踪信息确定有关异常原因和发生位置的信息。 堆栈跟踪:
[SocketException (0x274d): 由于目标机器积极拒绝,无法连接。 192.168.1.242:8086]
System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg) +2668969
System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type) +717
CSDN.Community.TopicFileDataCenter.TopicFileComponent.Reply(ReplyGenerateData rgd) +0
CSDN.Community.TopicFileDataCenter.TopicFileDataCenterEntry.Reply(Guid topicId, DateTime createDate, ReplyInfo reply, CommunityUser user, UserSectionProfile usp) +72
CSDN.Community.PointForum.Services.ReplyTopicManager.ReplyTopic(ReplyInfo reply, DateTime topicPostDate, String& errorInfo, CommunityUser user, UserSectionProfile usp) +659
CSDN.Community.PointForum.WebControls.ReplyTopicPage.bt_Submit_Click(Object sender, EventArgs e) +602
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1746
--------------------------------------------------------------------------------
版本信息: Microsoft .NET Framework 版本:2.0.50727.1433; ASP.NET 版本:2.0.50727.1433
--------------------------------------------------------------------------------由于目标机器积极拒绝,无法连接。 192.168.1.242:8086
说明: 执行当前 Web 请求期间,出现未处理的异常。请检查堆栈跟踪信息,以了解有关该错误以及代码中导致错误的出处的详细信息。 异常详细信息: System.Net.Sockets.SocketException: 由于目标机器积极拒绝,无法连接。 192.168.1.242:8086源错误: 执行当前 Web 请求期间生成了未处理的异常。可以使用下面的异常堆栈跟踪信息确定有关异常原因和发生位置的信息。 堆栈跟踪:
[SocketException (0x274d): 由于目标机器积极拒绝,无法连接。 192.168.1.242:8086]
System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg) +2668969
System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type) +717
CSDN.Community.TopicFileDataCenter.TopicFileComponent.Reply(ReplyGenerateData rgd) +0
CSDN.Community.TopicFileDataCenter.TopicFileDataCenterEntry.Reply(Guid topicId, DateTime createDate, ReplyInfo reply, CommunityUser user, UserSectionProfile usp) +72
CSDN.Community.PointForum.Services.ReplyTopicManager.ReplyTopic(ReplyInfo reply, DateTime topicPostDate, String& errorInfo, CommunityUser user, UserSectionProfile usp) +659
CSDN.Community.PointForum.WebControls.ReplyTopicPage.bt_Submit_Click(Object sender, EventArgs e) +602
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1746
--------------------------------------------------------------------------------
版本信息: Microsoft .NET Framework 版本:2.0.50727.1433; ASP.NET 版本:2.0.50727.1433
--------------------------------------------------------------------------------由于目标机器积极拒绝,无法连接。 192.168.1.242:8086
说明: 执行当前 Web 请求期间,出现未处理的异常。请检查堆栈跟踪信息,以了解有关该错误以及代码中导致错误的出处的详细信息。 异常详细信息: System.Net.Sockets.SocketException: 由于目标机器积极拒绝,无法连接。 192.168.1.242:8086源错误: 执行当前 Web 请求期间生成了未处理的异常。可以使用下面的异常堆栈跟踪信息确定有关异常原因和发生位置的信息。 堆栈跟踪:
[SocketException (0x274d): 由于目标机器积极拒绝,无法连接。 192.168.1.242:8086]
System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg) +2668969
System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type) +717
CSDN.Community.TopicFileDataCenter.TopicFileComponent.Reply(ReplyGenerateData rgd) +0
CSDN.Community.TopicFileDataCenter.TopicFileDataCenterEntry.Reply(Guid topicId, DateTime createDate, ReplyInfo reply, CommunityUser user, UserSectionProfile usp) +72
CSDN.Community.PointForum.Services.ReplyTopicManager.ReplyTopic(ReplyInfo reply, DateTime topicPostDate, String& errorInfo, CommunityUser user, UserSectionProfile usp) +659
CSDN.Community.PointForum.WebControls.ReplyTopicPage.bt_Submit_Click(Object sender, EventArgs e) +602
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1746
--------------------------------------------------------------------------------
版本信息: Microsoft .NET Framework 版本:2.0.50727.1433; ASP.NET 版本:2.0.50727.1433
---第一个
create proc CustomerLogin (@email varchar(50),@password varchar(50))
as
declare @pkID INT
set @pkid=-1
SELECT @pkID=PKID from Customers where email=@email and password=@password
select @pkid
go---第二个 PKID 不是唯一的吗?为什么还要用 @name?
create proc EditCustomer (@pkid int ,@email varchar(50),@NEWpassword varchar(50),oldpassword varchar(50),@name varchar(50))
as
SELECT pkid from Customers where pkid=@pkid and name=@name
if @@rowcount=0 return 1 --用户名不存在
SELECT pkid from Customers where pkid=@pkid and name=@name and password=@oldpassword
if @@rowcount=0 return 2 --密码错误update set password=@newpassword where pkid=@pkid and name=@name
if @@error=0 then return 0
go----后面两个说详细点
---第一个
create proc CustomerLogin (@email varchar(50),@password varchar(50))
as
declare @pkID INT
set @pkid=-1
SELECT @pkID=PKID from Customers where email=@email and password=@password
select @pkid
go---第二个 PKID 不是唯一的吗?为什么还要用 @name?
create proc EditCustomer (@pkid int ,@email varchar(50),@NEWpassword varchar(50),oldpassword varchar(50),@name varchar(50))
as
SELECT pkid from Customers where pkid=@pkid and name=@name
if @@rowcount=0 return 1 --用户名不存在
SELECT pkid from Customers where pkid=@pkid and name=@name and password=@oldpassword
if @@rowcount=0 return 2 --密码错误update set password=@newpassword where pkid=@pkid and name=@name
if @@error=0 then return 0
go----后面两个说详细点
---第一个
create proc CustomerLogin (@email varchar(50),@password varchar(50))
as
declare @pkID INT
set @pkid=-1
SELECT @pkID=PKID from Customers where email=@email and password=@password
select @pkid
go---第二个 PKID 不是唯一的吗?为什么还要用 @name?
create proc EditCustomer (@pkid int ,@email varchar(50),@NEWpassword varchar(50),oldpassword varchar(50),@name varchar(50))
as
SELECT pkid from Customers where pkid=@pkid and name=@name
if @@rowcount=0 return 1 --用户名不存在
SELECT pkid from Customers where pkid=@pkid and name=@name and password=@oldpassword
if @@rowcount=0 return 2 --密码错误update set password=@newpassword where pkid=@pkid and name=@name
if @@error=0 then return 0
go----后面两个说详细点
---第一个
create proc CustomerLogin (@email varchar(50),@password varchar(50))
as
declare @pkID INT
set @pkid=-1
SELECT @pkID=PKID from Customers where email=@email and password=@password
select @pkid
go---第二个 PKID 不是唯一的吗?为什么还要用 @name?
create proc EditCustomer (@pkid int ,@email varchar(50),@NEWpassword varchar(50),oldpassword varchar(50),@name varchar(50))
as
SELECT pkid from Customers where pkid=@pkid and name=@name
if @@rowcount=0 return 1 --用户名不存在
SELECT pkid from Customers where pkid=@pkid and name=@name and password=@oldpassword
if @@rowcount=0 return 2 --密码错误update set password=@newpassword where pkid=@pkid and name=@name
if @@error=0 then return 0
go----后面两个说详细点