Friday, July 9, 2010

Difference between Function and Stored Procedure?

  • User Defined Function (UDF) can be used in the SQL statements anywhere in the SELECT, WHERE, and HAVING section where as Stored procedures cannot be used.
  • Functions are designed to send their output to a query or T-SQL statement while Stored Procedures use EXECUTE or EXEC to run.
  • We can not use EXECUTE and PRINT commands inside a function but in SPROC
  • UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
  • UDFs can't change the server environment or your operating system environment, while a SPROC can.
  • Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
  • Stored Procedures are stored in compiled format in the database where as Functions are compiled and excuted runtime.
  • SPROC can be used with XML FOR Clause but Functions can not be.
  • SPROC can have transaction but not Functions.
  • Functions can be used in a SPROC but SPROC cann't be used in a Function. Only extended stored procedures can be called from a function.
  • Of course there will be Syntax differences and here is a sample of that
CREATE PROCEDURE dbo.ProcedureName
(
   @parameter1 datatype = DefaultValue,
   @parameter2 datatype OUTPUT
)
AS 
BEGIN
   T-SQL statements
   RETURN
END
GO

CREATE FUNCTION dbo.FunctionName
(
   @parameter1 datatype = DefaultValue,
   @parameter2 datatype
)
RETURNS datatype
AS
BEGIN
   SQL Statement
   RETURN Value
END
GO

1 comment:

  1. 20 main differences between Stored procedures and Functions in Sql Server
    http://www.webcodeexpert.com/2013/04/difference-between-stored-procedures.html

    ReplyDelete