Function Or Stored Procedure?

• Nitz

  SSC Journeyman

  Points: 95

  More actions

  August 6, 2002 at 10:07 am

  #105547

  HI ALL

  I have a basic dilemma, if I want to open an updateable recordset by of a parametered join query, should I use function or stored procedure? I noticed that when using function I have more update ability? Is this true? And what bout a snapshot recordset (read-only) that uses parameters, should I use sp or function?

  Thanks!!!

  
  Thanks!!!

• Steve Jones - SSC Editor

  SSC Guru

  Points: 737930

  More actions

  August 6, 2002 at 12:22 pm

  #434120

  I think it's a matter of design. functions are usually reused in your code somewhere and can be cleaner than calling a stored procedure (like in a select statement). Otherwise I think they function pretty closely.

  Steve Jones

  sjones@sqlservercentral.com

  http://www.sqlservercentral.com/columnists/sjones

• Andy Warren

  SSC Guru

  Points: 119902

  More actions

  August 6, 2002 at 1:12 pm

  #434133

  I typically use a stored proc to return data. Functions to package reusable code. I know that sounds the same, but its different to me! If Im returning a single record or just need a couple variables, I return it all as output parameters. Otherwise I return one or more recordsets. Whether a recordset is updatable depends on your client cursor type, not on how you got the data (well, almost, some views arent updatable).

  Andy

  http://www.sqlservercentral.com/columnists/awarren/

  Andy
  SQLAndy - My Blog!
  Connect with me on LinkedIn
  Follow me on Twitter