• PiMané (10/4/2012)


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

    ....

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

    The trigger is going to be fired anyway, teh data engine is going to be invoked anyway, the update is going to be logged anyway, because col1 is alwys updated. So having the 2 case expressions in the update statement to generate updates that either do or don't update col2 and col3 doesn't generate any noticeable overhead, may even be cheaper in performance terms than building dynamic SQL.

    Of course if you have an IF UPDATED(col2) condition in the trigger that wants to know whether col2 was actually changed you have to write it differently, but that is trivial for your update statement (and supertrivial if id is a unique key).

    Tom