简单存储过程 问题 下班结帖!

Question

ALTER PROCEDURE [dbo].[Pro_UpdateProductInfo]
(
@productID int --商品编号,
@title varchar(20),
@siteName varchar(50),
@site_id int,
@category_id int,
@Img varchar(50),
@cityName varchar(50),
@city_id int,
@pcode  int,
@provinceName varchar(50),
@description varchar(50),
@low_number int,
@et_price float,
@team_price float,
@begin_time datetime,
@end_time datetime
)
AS
BEGIN
update tg_teams set title=@title,siteName=@siteName,site_id=@site_id,category_id=@category_id,Img=@Img,cityName=@cityName,city_id=@city_id,pcode=@pcode,provinceName=@provinceName,
       description=@description,low_number=@low_number,et_price=@et_price,team_price=@team_price,begin_time=@begin_time,end_time=@end_time where id=@productID;
END
修改商品时 如果提供的参数为空时 则不修改(就是保留原来的值) 提供参数的值则更新 这个该怎么写 ？

Answer 1

BEGIN
    update tg_teams 
set title=isnull(@title,title),
siteName=isnull(@siteName,siteName),
....
end_time=isnull(@end_time,end_time)
where id=@productID;
END

Answer 2

BEGIN
if (@productID is not null )
begin 
update tg_teams 
set 
title=isnull(@title,title),
siteName=isnull(@siteName,siteName),
。
where id=@productID;
end 
END

Answer 3

update 
  tg_teams 
set 
  title=isnull(@title,title),
  siteName=isnull(@siteName,siteName),
  site_id=isnull(@site_id,site_id),
  category_id=isnull(@category_id,category_id),
  Img=isnull(@Img,Img),
  cityName=isnull(@cityName,cityName),
  city_id=isnull(@city_id,city_id),
  pcode=isnull(@pcode,pcode),
  provinceName=isnull(@provinceName,provinceName), 
  description=isnull(@description,description),
  low_number=isnull(@low_number,low_number),
  et_price=isnull(@et_price,et_price),
  team_price=isnull(@team_price,team_price),
  begin_time=isnull(@begin_time,begin_time),
  end_time=isnull(@end_time,end_time) 
where 
  id=@productID;

Answer 4

ALTER PROCEDURE [dbo].[Pro_UpdateProductInfo]
(
@productID int --商品编号,
@title varchar(20),
@siteName varchar(50),
@site_id int,
@category_id int,
@Img varchar(50),
@cityName varchar(50),
@city_id int,
@pcode  int,
@provinceName varchar(50),
@description varchar(50),
@low_number int,
@et_price float,
@team_price float,
@begin_time datetime,
@end_time datetime
)
AS
BEGINupdate 
  tg_teams 
set 
  title=isnull(@title,title),
  siteName=isnull(@siteName,siteName),
  site_id=isnull(@site_id,site_id),
  category_id=isnull(@category_id,category_id),
  Img=isnull(@Img,Img),
  cityName=isnull(@cityName,cityName),
  city_id=isnull(@city_id,city_id),
  pcode=isnull(@pcode,pcode),
  provinceName=isnull(@provinceName,provinceName), 
  description=isnull(@description,description),
  low_number=isnull(@low_number,low_number),
  et_price=isnull(@et_price,et_price),
  team_price=isnull(@team_price,team_price),
  begin_time=isnull(@begin_time,begin_time),
  end_time=isnull(@end_time,end_time) 
where 
  id=@productID;END

Answer 5

isnull
楼上几位都已经说清楚了

Answer 6

set col=isnull(@col,col) 的方式可行。

Answer 7

isnull 嘿嘿，
其实最好还是在程序段处理啦，
拼写SQL语句

Answer 8

用isnull函数，可以统一的这样写：字段值=isnull（@传入参数，字段值）
这个函数的意思是，如果第一个参数是null，则用返回第二个参数的值，否则就返回第一个参数的值。