Concept for updating large records.

Question

Concept for updating large records.

fryere

Ten Centuries

Points: 1054
More actions
January 12, 2005 at 1:49 pm

#164271

I need to update records that have 4 large text fields(2000-8000Chars) along with roughly 10 other text fields (100 max chars). Most of the time the user would only update one or two of the fields then click submit. No fields should be null.
Which update process would be best?
1) pass all fields into an Update SP.
2) Pass in Null values for the fields that the user didn't change, then use a dynamic SP to build the Sql statement only updating the fields that changed.
If @Field1 <> NULL
@strSql = strSql + ', Field1 =' + @Field1

Any thoughts?
Fryere
fryere

Viewing 3 posts - 1 through 2 (of 2 total)

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

Goce Smilevski SSCommitted Points: 1704 More actions · Answer 1

Hi Freyre,

due to the NOT NULL constraint on all columns, you can use NULL values as indicators that corresponding values did not change. But, I would suggest you not to use dynamic SQL. Try this instead: let's say that your update SP accepts one varchar parameter for each column (@large_texti for large text columns, i=1..4, @small_textj for small text columns, j=1..10).

update
  large_records
set
  large_text1 = isnull(@new_large_text1, large_text1),
  large_text2 = isnull(@new_large_text2, large_text2),
  large_text3 = isnull(@new_large_text3, large_text3),
  large_text4 = isnull(@new_large_text4, large_text4),
  small_text1 = isnull(@new_small_text1, small_text1),
  small_text2 = isnull(@new_small_text2, small_text2),
  small_text3 = isnull(@new_small_text3, small_text3),
  small_text4 = isnull(@new_small_text4, small_text4),
  small_text5 = isnull(@new_small_text5, small_text5),
  small_text6 = isnull(@new_small_text6, small_text6),
  small_text7 = isnull(@new_small_text7, small_text7),
  small_text8 = isnull(@new_small_text8, small_text8),
  small_text9 = isnull(@new_small_text9, small_text9),
  small_text10 = isnull(@new_small_text10, small_text10)
where
  pk_column = @pk_value

Regards,

Goce.

fryere Ten Centuries Points: 1054 More actions · Answer 2

Goce....I like it. It is much more clean and less complicated than trying to build the dynamic string. I will give it a try.

Thanks.

fryere