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)
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.