SQL Server 2005 - Functions and Procedures RRS feed

  • Question

  • Hi guys,

    Can anyone help me to undersand when do I use a function and when to use Procedures?

    Thanks in advance,
    Everything is possible, impossible just takes longer
    Wednesday, October 28, 2009 6:35 AM


  • Normally you do  stored procedures.

    Functions are significantly limited when compared to stored procedures, for example no database changes in UDF-s.

    While functions may be appealing for string manipulations, calculations or parametrized SELECTs, they take a performance hit frequently.

    One advantage functions have over sprocs is table-like return (table-valued functions).

    So you can do:

    select * from dbo.tvfGetData ('string', n)

    you cannot do:

    select * from sprocOmega

    Therefore, TVF-s can be applied as View replacements. View is basically a SELECT statement, TVF-s can have multi-statement logic.

    In the following script, sys.dm_exec_sql_text, a table-valued system function in the master database, used in CROSS APPLY. You cannot do that with a stored procedure (indirectly you can do it with OPENQUERY).

    FROM sys.dm_exec_requests r 
    sys.dm_exec_sql_text(r.sql_handle) AS st
    WHERE r.session_id > 50

    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    • Marked as answer by Zongqing Li Tuesday, November 3, 2009 4:50 AM
    Wednesday, October 28, 2009 6:59 AM

All replies