--创建修改客户信息存储过程
create procedure usp_update_ClientInfo
@CID int,
@CName varchar(20)='1',
@CAddress varchar(50)='2',
@Taxno varchar(10)='3',
@Repute varchar(10)='4',
@Country varchar(20) ='5',
@Province varchar(20)='6' ,
@PID int=7
as
if(@CName='1' and @CAddress ='2' and @Taxno='3' and @Repute = '4' and @Country ='5' and @Province='6' and @PID= 7)
begin
raiserror('您没有填写要修改的内容!',10,96)
return
end
else if (@CName<>'1')
update ClientInfo set CName = @CName where CID = @CID
else if (@CAddress<>'2')
update ClientInfo set CAddress = @CAddress where CID = @CID
else if (@Taxno<>'3')
update ClientInfo set Taxno = @Taxno where CID = @CID
else if (@Repute<>'4')
update ClientInfo set Repute = @Repute where CID = @CID
else if (@Country<>'5')
update ClientInfo set Country = @Country where CID = @CID
else if (@Province<>'6')
update ClientInfo set Province = @Province where CID = @CID
else if (@PID<>7)
update ClientInfo set PID = @PID where CID = @CID
go问一下还有没有其他的方法解决这个问题,怎么才能修改其中任何一个字段。
你只能执行 update ClientInfo set Province = @Province where CID = @CID
一个更新Province 而pid没有更新
--创建修改客户信息存储过程
IF EXISTS(SELECT * FROM sysobjects WHERE NAME='Proc_clientInfo_update')
BEGIN
DROP PROC Proc_clientInfo_update
END
GO
CREATE PROCEDURE Proc_clientInfo_update
@cid int,
@cname varchar(20)='1',
@caddress varchar(50)='2',
@ctaxno varchar(20)='3',
@repute varchar(10)='4',
@country varchar(10)='5',
@province varchar(10)='6'
AS
IF(@cname='1' AND @caddress='2' AND @ctaxno='3' AND @repute='4' AND @country='5' AND @province='6')
BEGIN
RAISERROR('信息没有改动!',10,101)
RETURN
END
IF(@cname<>'1')
BEGIN
UPDATE ClientInfo SET CName=@cname WHERE CID=@cid
END
IF(@caddress<>'2')
BEGIN
UPDATE ClientInfo SET cAddress=@caddress WHERE CID=@cid
END
IF(@ctaxno<>'3')
BEGIN
UPDATE ClientInfo SET Taxno=@ctaxno WHERE CID=@cid
END
IF(@repute<>'4')
BEGIN
UPDATE ClientInfo SET Repute=@repute WHERE CID=@cid
END
IF(@country<>'5')
BEGIN
UPDATE ClientInfo SET Country=@country WHERE CID=@cid
END
IF(@province<>'6')
BEGIN
UPDATE ClientInfo SET Province=@province WHERE CID=@cid
END
GO
没有错误额,我测试过了,这逻辑没问题,我在寻求其他的方法。
@CID int,
@set varchar(100)
as
begin
declare @sql varchar(1000)
set @sql = 'update ClientInfo ' + @set + ' where CID = ' + cast(@CID as varchar)
exec(@sql)
end
go其中@set的内容你得根据字段类型传入不同的值。