SQL动态更新表字段 传入字段可能为空
小技巧: 项目组有修改产品的基本信息字段 但有时候传入的字段可能为空 也可能不为空 动态修改表中字段.
1 USE [BetaProductMarket_DB] 2 GO 3 IF(EXISTS(SELECT * FROM dbo.sysobjects WHERE id=object_id(N'[dbo].[USP_SaveProductBaseInfo]') AND OBJECTPROPERTY(id, N'IsProcedure')= 1)) 4 BEGIN 5 DROP PROCEDURE [dbo].USP_SaveProductBaseInfo 6 END 7 GO 8 9 /* 10 作 者：Roni 11 功 能：修改产品基本信息 12 创建时间：2016-5-24 13 14 摘 要： 15 16 */ 17 18 CREATE PROCEDURE [dbo].USP_SaveProductBaseInfo 19 20 @ProductID INT, 21 @ProductType TINYINT=0, 22 @ProductName VARCHAR(200)=null , 23 @RecommendIndex TINYINT=0 , 24 @Issuer VARCHAR(200)=null, 25 @Period TINYINT=0 , 26 @Movement VARCHAR(300)=null , 27 @InvestmentDirection TINYINT=7, 28 @YieldsFixed DECIMAL(18, 4)=0 , 29 @YieldsFloat DECIMAL(18, 4)=0 , 30 @YieldsType TINYINT=0 , 31 @Amount DECIMAL(18, 4)=0 32 33 AS 34 35 BEGIN 36 37 38 UPDATE Tb_Product 39 SET UpdateTime=GETDATE(), 40 ProductName= CASE WHEN @ProductName IS NULL THEN Tb_Product.ProductName ELSE @ProductName END , 41 ProductType= CASE WHEN @ProductType=0 THEN Tb_Product.ProductType ELSE @ProductType END , 42 RecommendIndex = CASE WHEN @RecommendIndex =0 THEN Tb_Product.RecommendIndex ELSE @ProductType END , 43 Issuer= CASE WHEN @Issuer IS NULL THEN Tb_Product.Issuer ELSE @Issuer END , 44 Period= CASE WHEN @Period=0 THEN Tb_Product.Period ELSE @Period END , 45 Movement= CASE WHEN @Movement IS NULL THEN Tb_Product.Movement ELSE @Movement END , 46 InvestmentDirection= CASE WHEN @InvestmentDirection=7 THEN Tb_Product.InvestmentDirection ELSE @InvestmentDirection END , 47 YieldsFixed= CASE WHEN @YieldsFixed=0 THEN Tb_Product.YieldsFixed ELSE @YieldsFixed END , 48 YieldsFloat= CASE WHEN @YieldsFloat=0 THEN Tb_Product.YieldsFloat ELSE @YieldsFloat END , 49 YieldsType= CASE WHEN @YieldsType=0 THEN Tb_Product.YieldsType ELSE @YieldsType END , 50 Amount= CASE WHEN @Amount=0 THEN Tb_Product.Amount ELSE @Amount END 51 WHERE ProductID = @ProductID 52 53 54 55 56 57 58 END
