ALTER PROCEDURE [dbo].[UP_oask_User_Update]
@ID int,
@name nvarchar(50),
@pwd nvarchar(16),
@qq nvarchar(16),
@email nvarchar(50),
@question nvarchar(50),
@answer nvarchar(50),
@jifen int,
@fa int,
@qtfa int,
@touxian nvarchar(50),
@asknum int,
@replynum int,
@helpnum int,
@time datetime,
@del int,
@logintime datetime,
@jianjie text,
@PostNum int,
@UserGroup nvarchar(50),
@RegTime datetime,
@Sign nvarchar(100),
@LockUser tinyint,
@LoginCount int,
@LastIP nvarchar(40),
@Friends text,
@homepage nvarchar(100),
@FriendGroup nvarchar(100),
@userstatus tinyint,
@CheckAlter datetime,
@Rights nvarchar(100),
@favq nvarchar(100),
@sex tinyint,
@userico nvarchar(200),
@usertype int,
@UserPhoto nvarchar(255),
@UserTrue int,
@UserRandom nvarchar(50),
@UserRandomTime smalldatetime,
@UserBirthday datetime,
@UserPromary int,
@UserCity int,
@UserBlood int,
@UserWork nvarchar(255),
@UserQianMing text,
@PlNewFlag int,
@GuestNewFlag int
AS
UPDATE [oask_User] SET
[name] = @name,[pwd] = @pwd,[qq] = @qq,[email] = @email,[question] = @question,[answer] = @answer,[jifen] = @jifen,[fa] = @fa,[qtfa] = @qtfa,[touxian] = @touxian,[asknum] = @asknum,[replynum] = @replynum,[helpnum] = @helpnum,[time] = @time,[del] = @del,[logintime] = @logintime,[jianjie] = @jianjie,[PostNum] = @PostNum,[UserGroup] = @UserGroup,[RegTime] = @RegTime,[Sign] = @Sign,[LockUser] = @LockUser,[LoginCount] = @LoginCount,[LastIP] = @LastIP,[Friends] = @Friends,[homepage] = @homepage,[FriendGroup] = @FriendGroup,[userstatus] = @userstatus,[CheckAlter] = @CheckAlter,[Rights] = @Rights,[favq] = @favq,[sex] = @sex,[userico] = @userico,[usertype] = @usertype,[UserPhoto] = @UserPhoto,[UserTrue] = @UserTrue,[UserRandom] = @UserRandom,[UserRandomTime] = @UserRandomTime,[UserBirthday] = @UserBirthday,[UserPromary] = @UserPromary,[UserCity] = @UserCity,[UserBlood] = @UserBlood,[UserWork] = @UserWork,[UserQianMing] = @UserQianMing,[PlNewFlag] = @PlNewFlag,[GuestNewFlag] = @GuestNewFlag
WHERE ID=@ID
这个我要修改其中的一列 其他的都列就全部变成了null 恼人!
UPDATE [oask_User]
SET
[name] = @name
WHERE ID=@ID
ALTER PROCEDURE [dbo].[UP_oask_User_Update]
@UserTrue int,
@ID int
if @UserTrue=1
begin
Update [oask_User] SET
[UserTrue]=@UserTrue
WHERE ID=@ID
end
else
begin
@ID int,
@name nvarchar(50),
@pwd nvarchar(16),
@qq nvarchar(16),
@email nvarchar(50),
@question nvarchar(50),
@answer nvarchar(50),
@jifen int,
@fa int,
@qtfa int,
@touxian nvarchar(50),
@asknum int,
@replynum int,
@helpnum int,
@time datetime,
@del int,
@logintime datetime,
@jianjie text,
@PostNum int,
@UserGroup nvarchar(50),
@RegTime datetime,
@Sign nvarchar(100),
@LockUser tinyint,
@LoginCount int,
@LastIP nvarchar(40),
@Friends text,
@homepage nvarchar(100),
@FriendGroup nvarchar(100),
@userstatus tinyint,
@CheckAlter datetime,
@Rights nvarchar(100),
@favq nvarchar(100),
@sex tinyint,
@userico nvarchar(200),
@usertype int,
@UserPhoto nvarchar(255),
@UserTrue int,
@UserRandom nvarchar(50),
@UserRandomTime smalldatetime,
@UserBirthday datetime,
@UserPromary int,
@UserCity int,
@UserBlood int,
@UserWork nvarchar(255),
@UserQianMing text,
@PlNewFlag int,
@GuestNewFlag int
AS
UPDATE [oask_User] SET
[name] = @name,[pwd] = @pwd,[qq] = @qq,[email] = @email,[question] = @question,[answer] = @answer,[jifen] = @jifen,[fa] = @fa,[qtfa] = @qtfa,[touxian] = @touxian,[asknum] = @asknum,[replynum] = @replynum,[helpnum] = @helpnum,[time] = @time,[del] = @del,[logintime] = @logintime,[jianjie] = @jianjie,[PostNum] = @PostNum,[UserGroup] = @UserGroup,[RegTime] = @RegTime,[Sign] = @Sign,[LockUser] = @LockUser,[LoginCount] = @LoginCount,[LastIP] = @LastIP,[Friends] = @Friends,[homepage] = @homepage,[FriendGroup] = @FriendGroup,[userstatus] = @userstatus,[CheckAlter] = @CheckAlter,[Rights] = @Rights,[favq] = @favq,[sex] = @sex,[userico] = @userico,[usertype] = @usertype,[UserPhoto] = @UserPhoto,[UserTrue] = @UserTrue,[UserRandom] = @UserRandom,[UserRandomTime] = @UserRandomTime,[UserBirthday] = @UserBirthday,[UserPromary] = @UserPromary,[UserCity] = @UserCity,[UserBlood] = @UserBlood,[UserWork] = @UserWork,[UserQianMing] = @UserQianMing,[PlNewFlag] = @PlNewFlag,[GuestNewFlag] = @GuestNewFlag
WHERE ID=@ID
end消息 156,级别 15,状态 1,过程 UP_oask_User_Update,第 11 行
关键字 'if' 附近有语法错误。
消息 102,级别 15,状态 1,过程 UP_oask_User_Update,第 19 行
'@ID' 附近有语法错误。
消息 137,级别 15,状态 2,过程 UP_oask_User_Update,第 68 行
必须声明标量变量 "@name"。感觉没有错啊
SP 是固定形参列表的,所以LZ想说如果@UserTrue=1使用参数列表A,否则使用参数列表B这种做法是不可行的.
1. 在Update子句的Set部分用IsNull(@XXX,XXX)来代替@XXX,如
Update ...
Set name=IsNull(@name,name),...
Where ...不过这个方法中如果字段很长,这个Update语句的性能就不好了。
比方说如果name这个字段的长度是1024,实际传入的@name为空代表不需要更改,但是Update语句还是会老老实实的改这个字段。2. 用一堆的if语句来进行Update,如
If (@name is not null)
Update ... Set name= @name Where...
If (@pwd is not null)
update ... Set pwd=@pwd Where...
但是如果要修改的字段数很多,则需要执行好多次的Update语句,性能依然不好。LZ可以根据自己的需要来选择其中1种,或者将2者混合起来(把所有的短字段用方法1的1条Update语句进行处理,把长字段用方法2分别处理)