User Defined Functions vs Stored Procedures

• Bill Cockerill

  SSC Enthusiast

  Points: 195

  More actions

  February 3, 2004 at 7:06 am

  #106876

  Dear Forum,

  I've recently discovered user defined functions (UDFs) and find them very useful for calculating new variables and putting views in.

  I've read about stored procedures (SPs) which seem to be similar in the way they are written.

  However, I haven't been able to find information about when to use a UDF or a SP.

  Are there situations where it is much more appropriate to use a UDF over a SP?

  Is this a gey area?

  Are SPs faster but harder to write?

  Thanks, Bill

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  February 3, 2004 at 7:17 am

  #492610

  There is no magic about udf's or stored procedures

  create procedure showsp

  as

  select * from ::fn_helpcollations()

  go

  exec showsp

  drop procedure showsp

  But as this is a very broad topic, I suggest to first take a look at BOL for both.

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