如果存储过程中的参数为null，则不更新列

I have the following stored procedure.

我有以下存储过程。

Whenever @Logo is null, the current value is erased. I'd like to not update the value for Logo if @Logo is NULL.

每当@Logo为null时,将删除当前值。如果@Logo为NULL,我想不更新Logo的值。

IF OBJECT_ID ('kii.p_UpdateDocumentStyle') IS NOT NULL
   DROP PROCEDURE kii.p_UpdateDocumentStyle
GO

CREATE PROCEDURE kii.p_UpdateDocumentStyle
   @DocumentId AS INT,
   @TitleForegroundColor AS NVARCHAR(10),
   @TitleBackgroundColor AS NVARCHAR(10),
   @TitleFontFamily AS NVARCHAR(50),
   @TitleFontSize AS NVARCHAR(10),
   @TitleFontStyle AS NVARCHAR(10),
   @TitleFontWeight AS NVARCHAR(10),
   @TitleTextDecoration AS NVARCHAR(15),
   @SectionTitleForegroundColor AS NVARCHAR(10),
   @SectionTitleBackgroundColor AS NVARCHAR(10),
   @SectionTitleFontFamily AS NVARCHAR(50),
   @SectionTitleFontSize AS NVARCHAR(10),
   @SectionTitleFontStyle AS NVARCHAR(10),
   @SectionTitleFontWeight AS NVARCHAR(10),
   @SectionTitleTextDecoration AS NVARCHAR(15),
   @ParagraphForegroundColor AS NVARCHAR(10),
   @ParagraphBackgroundColor AS NVARCHAR(10),
   @ParagraphFontFamily AS NVARCHAR(50),
   @ParagraphFontSize AS NVARCHAR(10),
   @ParagraphFontStyle AS NVARCHAR(10),
   @ParagraphFontWeight AS NVARCHAR(10),
   @ParagraphTextDecoration AS NVARCHAR(15),
   @Logo AS Image = NULL
AS

UPDATE kii.DocumentStyle
SET 
    TitleForegroundColor = @TitleForegroundColor,           
    TitleBackgroundColor = @TitleBackgroundColor,           
    TitleFontFamily = @TitleFontFamily,             
    TitleFontSize = @TitleFontSize,                 
    TitleFontStyle = @TitleFontStyle,                   
    TitleFontWeight = @TitleFontWeight,             
    TitleTextDecoration = @TitleTextDecoration,         
    SectionTitleForegroundColor = @SectionTitleForegroundColor, 
    SectionTitleBackgroundColor = @SectionTitleBackgroundColor, 
    SectionTitleFontFamily = @SectionTitleFontFamily,           
    SectionTitleFontSize = @SectionTitleFontSize,           
    SectionTitleFontStyle = @SectionTitleFontStyle,         
    SectionTitleFontWeight = @SectionTitleFontWeight,           
    SectionTitleTextDecoration = @SectionTitleTextDecoration,       
    ParagraphForegroundColor = @ParagraphForegroundColor,       
    ParagraphBackgroundColor = @ParagraphBackgroundColor,       
    ParagraphFontFamily = @ParagraphFontFamily,         
    ParagraphFontSize = @ParagraphFontSize,             
    ParagraphFontStyle = @ParagraphFontStyle,               
    ParagraphFontWeight = @ParagraphFontWeight,         
    ParagraphTextDecoration = @ParagraphTextDecoration,     
    Logo = @Logo                            
WHERE
    DocumentId = @DocumentId
GO

GRANT EXECUTE on kii.p_UpdateDocumentStyle TO p_role_kii
GO

2 个解决方案

#1

Amend your line to this

修改你的路线

Logo = COALESCE(@logo, Logo)

COALESCE will assign a value to Logo that is: @logo if it was populated, otherwise it assigns the existing value of Logo

COALESCE将为Logo分配一个值:@logo如果已填充,否则它将分配Logo的现有值

#2

You should be able to use a case statement:

您应该能够使用案例陈述:

Logo = CASE WHEN (@Logo is null) 
            THEN Logo
       ELSE @Logo
     END

#1