• deepkt (7/10/2013)


    Thanks for the reply.

    In UI I am binding data to a grid.. all the logic will be processed using stored proc and from back end.

    In UI user will modify Displayorder of his interest and it will be passed to SP and need to process it.

    If multiple updates are there then multiple times proc will be called. I am fine with Multiple calls to database.

    And that's where your problems start. I suggested using a TVP for two reasons:

    • It's more efficient. You get a single round-trip to the database, instead of doing many calls. This is not the least important if you later would move the database to the cloud or somewhere else where there is a long latency between client and database.
    • It's simpler. The current logic you have is very difficult to get right, as you already have experienced. The first call goes right, but the second call gets messed up, because you are likely to make it from the situation before the first call. To compensate for this the client has to compensate for the logic in the stored procedure, or the stored procedure has to compensate for the logic in the client. Neither of these are good software-engineering practices, as different modules should be decoupled from each other as much as possible.

    With the solution I suggest, the stored procedure is dirt simple: it's a single update from the TVP. Well, maybe a single MERGE statement as the user may add or delete rows as well. You need to some more work in the client - but not a lot. I don't know anything about grid-binding, but I assume that you can bind a DataTable to the grid, and you can pass a DataTable to a TVP.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]