sql – 如果存储过程中的参数为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

解决方法

Logo = COALESCE(@logo,Logo)

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!