Difference between function and store procedure in SQL Server Database.

 

The difference between UDF[User Defined Function] and SP[Store Procedure] in SQL Server Database:

1, UDF can only have input parameters, however, SP can both have input and output parameters.

2, Function must return value and must single value with return word, store procedure can return [n] values without return word. ( n>=0 ). When there are some output parameters in the store procedure then the n will be greatet then one.

     eg: create proc testProc

          (

          @testId int,

          @statusCode int output

          )

           as

          begin

               declare @ret int

               set @statusCode = 200

               select @ret

          end

3, We can use functions in select query statement, and cannot use store procedures in select query statement.

     eg: select a.testId, dbo.myFun(a.testId) as serialName from Test_Table a;

4, Store Procedure is faster than Functions.

5, We can use variable table and temporary table in Store Procedure, but only can use variable table in user defined function.

6, Still thinking...