>>>>>By default all declared variables will be NULL. Assuming you will not be having NULL values in your XML file you can do
>>>>>
>>>>>update myTable SET Fld1Name = COALESCE(@Fld1Value, Fld1Name), etc.
>>>>
>>>>I didn't know that I can skip declaring a variable. I thought that I always have to do
>>>>declare @MyVarName INT before using it.
>>>>
>>>>I have never used COALESCE() either. The approach you describe should work. I will test it.
>>>>Thank you.
>>>
>>>This is not what I said. You can not skip DECLARE in SQL Server, all variables must be declared. Once a variable is declared, it will be NULL. The select @FidlVar = ...
>>>doesn't change the value if that select statement returns 0 rows. So, if you never going to have NULL values passed in your XML, then the solution I suggested is the simplest one.
>>>
>>>The alternative is to build the dynamic SQL which is also simple.
>>
>>Thank you for clarifying my misunderstanding. I will check if any of my passed valued could be NULL. The approach with COALESCE() seems to be simpler than dynamic SQL (which I know nothing about).
>
>Building dynamic SQL is simple as well, e.g.
>
>declare @SQL nvarchar(max);
>
>set @SQL = '';
>declare @Fld1Val int, ....
>
>select @Fld1Val = ....
>
>IF @@ROWCOUNT >0
> begin
> set @SQL = @SQL + ',
>Fld1 = @Fld1Val'
> end
>
>....
>IF LEN(@SQL) > 0
> BEGIN
> set @SQL = 'UPDATE myTable SET ' + SUBSTRING(@SQL, 2, LEN(@SQL))
> execute sp_executeSQL @SQL, N'@Fld1Val int, @Fld2Val int', @Fld1Val, @Fld2Val
> END
>
>
Thank you for the example of dynamic SQL.
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham