Post reply

avoiding dynamic sql for updating

  • April 8, 2010 at 12:43 pm

    #233498

    Is there anyway to update a specific column based on a variable like the following....

    declare @ColumnName varchar(50)

    declare @FieldValue varchar(50)

    update table set @ColumnName = @FieldValue

    without using dynamic sql or endless case statements?

  • April 8, 2010 at 12:47 pm

    #1148571

    Why is it important to avoid dynamic SQL?

  • April 8, 2010 at 12:56 pm

    #1148582

    I'm not totally against using dynamic sql.

    But it can become more difficult to debug and maintain over time.

    Also, there can be a performance loss.

  • April 8, 2010 at 1:06 pm

    #1148591

    weezie (4/8/2010)

    I'm not totally against using dynamic sql.

    But it can become more difficult to debug and maintain over time.

    Also, there can be a performance loss.

    It can be simpler to maintain than having 50 variations of a query coded in a stored procedure.

    There is nothing inherent in dynamic SQL that results in performance loss compared to static SQL, and there are many situations where it will be better.

  • April 8, 2010 at 1:25 pm

    #1148612

    Given the choice of:

    if @Param1 > '' and @Param2 > '' update table1 set column1 = @param1, column2 = @param2

    else if @Param1 > '' update table1 set column1 = @param1

    else if @Param2 > '' update table1 set column2 = @param2

    or

    update table1

    set column1 = case when @param1 > '' then @param1 else column1 end,

    column2 = case when @param2 > '' then @param2 else column2 end

    I know you said you didn't want case statements, but I'll take the second version.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply