Dynamic Update Stored Proc

• dronator2k

  SSC Veteran

  Points: 240

  More actions

  June 19, 2003 at 2:49 pm

  #106175

  I've got a question that we've been having here in the office for awhile. We have the need to update a customer record but we're not sure which columns will be altered. This is what we have presently:

  ------------------------------------------------

  CREATE Procedure ap_BASE_Accounts_Update

  @pcCustID Char(8),

  @pvNameF VarChar(15),

  @pvNameL VarChar(25),

  @pvAddress1 VarChar(35),

  @pvAddress2 VarChar(35),

  @pvCity VarChar(25),

  @pvState VarChar(10),

  @pvZip VarChar(12),

  AS

  -- Update the record

  UPDATE [800contacts].dbo.customer

  set NameF = isnull(@pvNameF,NameF),

  NameL = isnull(@pvNameL,NameL),

  Address1 = isnull(@pvAddress1,Address1),

  Address2 = isnull(@pvAddress2,Address2),

  City = isnull(@pvCity,City),

  State = isnull(@pvState,State),

  Zip = isnull(@pvZip,Zip)

  where CustID = @pcCustID

  --------------------------------------------------

  Now, a few questions.

  1.) If the parameter is NULL, does SQL still update the column with the data already in there? (i.e. set Namel = Namel)

  2.) Does the fact that an entire row might be updated outweigh having to perform Dynamic SQL or a Pass through of the static info since it is within a SP? Note: This query will be performed a LOT.

  3.) Is there a better way to perform this within a SP? Surely we're not the first ones to come across this scenario.

  Thanks in advance for all of your assistance!

  Adrian Porter

  
  Adrian Porter

• mworku

  Ten Centuries

  Points: 1311

  More actions

  June 19, 2003 at 5:32 pm

  #462310

  1, Yes, if any of your param. is NULL existing col. value will be used

  But non of your parmas. can't be NULL as you have it now

  If you don't care as to which col. get updated,

  all non-required params. should be initialized to a constant value

  in this case (NULL)

  @pvNameF VarChar(15) = NULL,

  @pvNameL VarChar(25) = NULL,

  and so on ....

  2, SP is better than dynamic SQL, b/c after the initial execution

  the server would cache the optimized query plan for later use.

  Also if custid is the primary key or if you have an index on it

  you'll see improved performance.

  All the function calls are done inline, therefore overhead is minimal

  3, I think what you have is as good as it gets

  MW

  
  MW