UpDate using T-SQL

  • New here, I'm looking around and have not found this yet, so I'm going to ask this as a question. I'm working on writing a update t-sql statement as a stored procedure, but the update fields need to unknown, that is, could be 5 fields this time, maybe 20 fields next time. Although ADO can do this, I'm trying to push this back to the database server, anyone have any thoughts?

  • Hi TaffyLewis,

    quote:


    that is, could be 5 fields this time, maybe 20 fields next time.


    5 fields to be updated, or 5 to choose from which is updated?

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • If I understand you here, you want to update a single row in a table, but you only want to change certain fields, and the fields that you want to change can be different each time?

    Here is a (inefficient and horrible) way to achieve this. Basically you pass in the ID of the row to be updated (or whatever you need to get it) and the update stored procedure takes ALL of the fields that can be updated as optional parameters.

    CREATE PROCEDURE testUpdate @intID as int,

    @intCol1 as int=null,

    @intCol2 as int=null

    AS

    IF @intCol1 is not null UPDATE tableb SET col1=@intCol1 WHERE ID=@intID

    IF @intCol2 is not null UPDATE tableb SET col2=@intCol2 WHERE ID=@intID

    ... repeat for each updatable field ...

    So:

    testUpdate @intID=1, @col1=1 just changes col1

    testUpdate @intID=1, @col2=1 just changes col2

    testUpdate @intID=1, @col1=1, @col2=1 changes both col1 and col2.

  • With ADO, I guess that you are building the update statement on the client.

    There is similar facility within SQL Server called dynamic sql. This is where you create an sql string within a stored procedure and then execute it.

    WARNING (Frank is very hot on this). Using dynamic SQL does have a number of health warnings. In particular you need to consider SQL Injection attacks - there have been a number of posts on this site about injection attacks and dynamic SQL so go have a read.

    The main drawback with dynamic SQL is that you have to give appropriate permissions for the user on the table ie insert, update, delete etc. With stored procedures, you can normally avoid this by giving users execute permission on the procedure only.

    If you were planning to build the update statement on the client then you would have to grant appropriate permissions on the table so using dynamic sql is no different in that respect.

    On a completly different tack, you might be able to avoid build an sql statement (either on the client or on the server). If the list of fields is finite, you could build a static stored procedure along the following lines:

    create procedure update_table @col1 varchar(25) = null, @col2 varchar(25) = null, @col3 varchar(25) = null.... as

    update table

    set col1 = isNull(@col1,col1), col2 = isNull(@col2,col2), col3 = isNull(@col3,col3),,,,

    where ...

    When you call a procedure, you do not have to pass all the parameters:

    exec update_table @col1 = 'New value for col1', @col3 = 'New value for col3'

    When you call the procedure with col1 and col3 values, it updates the entire row but as @col2 is null, it is updated to the same value ie no change.

    Food for thought

    Jeremy

  • >>With ADO, I guess that you are building the update statement on the client.

    In DAO you can do something like this to a recordset object.

    rst.edit

    rst.fields("Name")="Bungle"

    rst.fields("SexualOrientation")="n/a"

    ...

    ...

    rst.update

    I'm pretty sure it's the same in ADO. Thinking about it, this probably does result in you producing an update statement but it's all nicely abstracted away in the update method.

    >>update table

    >>set col1 = isNull(@col1,col1), col2 = isNull(@col2,col2), col3 = isNull(@col3,col3),,,,

    >>where ...

    Oh yes, of course <ahem> somewhat neater than my suggestion - nice one.

  • Does this serve your needs if you put into the procedure the following statement?

    update T set

    a = case when @a is null then a else @a end,

    b = case when @b-2 is null then b else @b-2 end

    where ID = @ID

    @ID, @a, @b-2 are parameters in procedure.

  • Jeremy

    I like your solution. I have had a similiar situation but I did not want to use dynamic sql to solve it. So, I ended up using a solution similiar to Planet115. It worked, but I thought there had to be a better solution. Thanks for the enlightenment.

  • You can use a case statement and set the value back to itself if it doesn't fit certain criteria. An example below...

    /*--------------------------------

    Updates #Test1 with values from #Test2 only when the #Test2 values

    are 10 times #Test1 values

    */--------------------------------

    Create table #Test1 (intID int identity, col1 int, col2 int, col3 int)

    Insert #Test1 values (100, 100, 100)

    Insert #Test1 values (200, 300, 400)

    Create table #Test2 (intID int identity, col1 int, col2 int, col3 int)

    Insert #Test2 values (1000, 1000, 569)

    Insert #Test2 values (2000, 5489, 4000)

    Updatet1

    Sett1.col1= Case

    When t2.col1/10 = t1.col1 then t2.col1

    Else t1.col1--This sets the values back to itself

    End,

    t1.col2= Case

    When t2.col2/10 = t1.col2 then t2.col2

    Else t1.col2

    End,

    t1.col3= Case

    When t2.col3/10 = t1.col3 then t2.col3

    Else t1.col3

    End

    From#Test1 t1 (nolock)

    JOIN#Test2 t2 (nolock) on t1.intID = t2.IntID

    --record 1 col3 not updated because 569/10 <> 100

    --same for record 2 col2

    select * from #Test1

    Signature is NULL

Viewing 8 posts - 1 through 7 (of 7 total)

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