如何在存储过程中将xml与字符串进行比较? (MS SQL) [英] How do i compare xml to string in a stored procedure? (MS SQL)

问题描述

嘿，我有问题，
我正在编写用于更新的通用存储过程.
我只需要更新非默认字段，所以我进行了动态查询，例如thid:

hey, i have a problem,
i''m writing a generic stored procedure for update.
i need to update only non default fields, so i made a dynamic query, something like thid:

set @vsSQL =		   ''UPDATE Records set '' + char(10)
		set @vsSQL = @vsSQL +  ''updateStatusDate='' + @updateTime + char(10)
		if (@originalBody <> ''<empty />'')
			set @vsSQL = @vsSQL +  '',originalBody='' + @originalBody + char(10)
		if (@modelBody <> ''<empty />'')
			set @vsSQL = @vsSQL +  '',modelBody='' + @modelBody + char(10)
		if (@transformatedBody <> ''<empty />'')
			set @vsSQL = @vsSQL +  '',transformatedBody='' + @transformatedBody + char(10)
		if (@orchestrationID is not null)	
			set @vsSQL = @vsSQL +  '',orchestrationID='' + @orchestrationID + char(10)
		set @vsSQL = @vsSQL +  ''where recId='' + @InputRecordID + char(10)	

问题在于粗体字，
@originalBody是xml的类型，我无法检查其是否不等于空.
所以我有2个问题:

1.我该如何检查?尝试使用Xquery，没有运气.
2.我需要将xml转换为字符串，对于动态查询，我不喜欢
因为它可能会遇到性能问题.
关于解决通用更新存储过程还有其他想法吗?

谢谢！

Ariel.

the problem is in the bolded line,
@originalBody is type of xml, and i cannot check if its not equal to empty.
so i''ve got 2 questions:

1. how do i check it? tried using Xquery, no luck.
2. i need to convert the xml to string, for the dynamic query, i don''t like that
because it will probably suffer from performence issues.
any other thoughts about addressing the generic update stored proc?

Thanks!

Ariel.

推荐答案

我不喜欢这样，因为它可能会遇到性能问题."

但是，在性能影响方面，要进行如此多的字符串连接将是微不足道的.

"i don''t like that because it will probably suffer from performence issues."

And yet you have so much string concatenation going on the performance hit will be negligible.

可以通过以下方式做到这一点:

k ive done it this way:

update Records set
        updateStatusDate=@updateTime,
        originalBody =
            case when ((datalength(@originalBody) >= 10) or (@originalBody is null)) then @originalBody
            else originalBody
            END,
        modelBody =
            case when ((datalength(@modelBody) >= 10) or (@modelBody is null)) then @modelBody
            else modelBody
            END,
        transformatedBody =
            case when (datalength(@transformatedBody) >= 10 or (@transformatedBody is null)) then @transformatedBody
            else transformatedBody
            END,
        orchestrationID =
            case when (@orchestrationID is not null) then @orchestrationID
            else orchestrationID
            END
        where recId=@InputRecordID

这篇关于如何在存储过程中将xml与字符串进行比较? (MS SQL)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！