Hi,

Regarding Dynamic SQL....

Instead of a SELECT suppose I have an UPDATE used on a SP.

The SP receives 6 parameters: @RecordId, @Col1Value, @Col2Update, @Col2Value, @Col3Update, @Col3Value...

Col1 is always updated but Col2 and Col3 are only updated if Col2Update and Col3Update are true (1).

Should dynamic SQL be used here as well?

I could write

UPDATE table SET

Col1 = @Col1Value,

Col2 = CASE WHEN @Col2Update = 1 THEN @Col2Value ELSE Col2 END,

Col3 = CASE WHEN @Col3Update = 1 THEN @Col3Value ELSE Col3 END

WHERE Id = @RecordId

but this would make Col2 and Col3 always be updated no matter what, even if the value is themselves...

also it's possible to write:

UPDATE table SET Col1 = @Col1Value WHERE Id = @RecordId

IF @Col2Update = 1

UPDATE table SET Col2 = @Col2Value WHERE Id = @RecordId

....

This would make another seek and update row and if the table had an UPDATE trigger it would fire the trigger again...

Back to the 1st case if there was also a trigger with the condition IF UPDATED(Col2) the condition would always be true...

There also the long solution:

IF @Col2Update = 1 AND @Col3Update = 1

....

ELSE

IF @Col2Update = 1

ELSE

.....

IF @Col3Update = 1

.....

ELSE

...

This is long and if necessary to add another column it would even longer... 2^[optional parameters]..

So is dynamic SQL a good option for this case too?!

Thanks,

Pedro