>>>>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
If it's not broken, fix it until it is.
My Blog