SQL动态更新表字段 传入字段可能为空

 小技巧: 项目组有修改产品的基本信息字段 但有时候传入的字段可能为空 也可能不为空  动态修改表中字段.

USE [BetaProductMarket_DB]
GO
IF(EXISTS(SELECT * FROM dbo.sysobjects WHERE id=object_id(N'[dbo].[USP_SaveProductBaseInfo]') AND OBJECTPROPERTY(id,   N'IsProcedure')= 1))
BEGIN
    DROP PROCEDURE [dbo].USP_SaveProductBaseInfo
END
GO

/*
作    者：Roni
功    能：修改产品基本信息
创建时间：2016-5-24

摘    要：
    
*/

CREATE PROCEDURE [dbo].USP_SaveProductBaseInfo

    @ProductID INT,
    @ProductType TINYINT=0,
    @ProductName VARCHAR(200)=null ,
    @RecommendIndex TINYINT=0     ,
    @Issuer VARCHAR(200)=null,
    @Period TINYINT=0    ,
    @Movement VARCHAR(300)=null ,
    @InvestmentDirection TINYINT=7,
    @YieldsFixed   DECIMAL(18, 4)=0    ,
    @YieldsFloat   DECIMAL(18, 4)=0     ,
    @YieldsType     TINYINT=0    ,
    @Amount    DECIMAL(18, 4)=0     
 
AS

BEGIN

    
UPDATE Tb_Product   
SET  UpdateTime=GETDATE(),
        ProductName= CASE WHEN @ProductName IS NULL THEN Tb_Product.ProductName ELSE @ProductName END ,
        ProductType= CASE WHEN @ProductType=0 THEN Tb_Product.ProductType ELSE @ProductType END   ,
        RecommendIndex = CASE WHEN @RecommendIndex =0 THEN Tb_Product.RecommendIndex ELSE @ProductType END     ,
        Issuer= CASE WHEN @Issuer IS NULL THEN Tb_Product.Issuer ELSE @Issuer END ,
        Period= CASE WHEN @Period=0 THEN Tb_Product.Period ELSE @Period END   ,
        Movement= CASE WHEN @Movement IS NULL THEN Tb_Product.Movement ELSE @Movement END ,
        InvestmentDirection= CASE WHEN @InvestmentDirection=7 THEN Tb_Product.InvestmentDirection ELSE @InvestmentDirection END   ,
        YieldsFixed= CASE WHEN @YieldsFixed=0 THEN Tb_Product.YieldsFixed ELSE @YieldsFixed END   ,
        YieldsFloat= CASE WHEN @YieldsFloat=0 THEN Tb_Product.YieldsFloat ELSE @YieldsFloat END   ,
        YieldsType= CASE WHEN @YieldsType=0 THEN Tb_Product.YieldsType ELSE @YieldsType END   ,
        Amount= CASE WHEN @Amount=0 THEN Tb_Product.Amount ELSE @Amount END   
WHERE ProductID = @ProductID




      

END