How do i compare xml to string in a stored procedure? (MS SQL)

Question

4.00/5 (1 vote)

See more:

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.

Posted 30-Jun-11 5:24am

arielb

Add a Solution

2 solutions

Solution 1

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

Posted 30-Jun-11 10:13am

Not Active

Comments

arielb 2-Jul-11 12:56pm

obvious.
so thats way i asked, is there any best practice to do a generic update stored proc?
something like
update X
set
A = @a if @a != null
B = @b if @b != null

the only thing i could think of, is to select the previous values, and use a case in every set update.

[no name] 2-Jul-11 14:39pm

You can use a where clause, such as

UPDATE myTable
SET field1 = @field1
WHERE @field2 IS NOT NULL

Or you can use case/when

UPDATE myTable
SET field1 = @field1,
CASE @field2
WHEN IS NOT NULL
THEN field2 = @field2
END,
field3 = @field3

arielb 4-Jul-11 2:57am

hey, i tried your second solution (which is the best 4 me)

UPDATE tblTest
SET f1 = @f1,
CASE @f2 WHEN IS NOT NULL
THEN f2 = @f2 END,
f3 = @f3

when the table has f1, f2, f3 as fields.

it gives me:

Msg 156, Level 15, State 1, Procedure ariel, Line 20
Incorrect syntax near the keyword 'CASE'.

anyway, this worked for me:
UPDATE tblTest
set f2 =
case when (@f2 is not NULL) then @f2
else f2
END
where f1 = 1;

but isnt that an issue? because, if the value is null, it will update the last value (though i dont want it to do so)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

arielb · Accepted Answer · 2011-07-10T21:42:00

k ive done it this way:

SQL

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