Difference between User-defined function and Store Procedure

Hello friends today i was working on some store procedure and there was a need to create a function which need to be accessed inside the store procedure. We have done that many times in the past and will be doing it in future too.

But usually it's a good idea to some time sit back and relax by going through the things that we already have done and know it.Thinking that i am writing here today the differences between UDFs and Store Procedure, Would be trying to explain both in brief also.

• Procedure can return zero or n values whereas function can return one value which is mandatory.
• Procedures can have input/output parameters for it whereas functions can have only input parameters.
• Procedure allows select as well as DML statement in it whereas function allows only select statement in it.
• Functions can be called from procedure whereas procedures cannot be called from function.
• Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.
• We can go for transaction management in procedure whereas we can't go in function.
• Procedures can not be utilized in a select statement whereas function can be embedded in a select statement.
• UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
• UDFs that return tables can be treated as another row-set. This can be used in JOINs with other tables. Gives developer the ability to break out complex logic into shorter and shorter code blocks, which makes the code more readable, less complex and easy to maintain.
• Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowset-operations. This ability to use the function anywhere yu can use a scalar of the same data type is very powerful thing.

Stored Procedure

A Stored Procedure is a program (or procedure) which is physically stored within a database. They are usually written in a proprietary database language like PL/SQL for Oracle database or PL/PgSQL for PostgreSQL. The advantage of a stored procedure is that when it is run, in response to a user request, it is run directly by the database engine, which usually runs on a separate database server. As such, it has direct access to the data it needs to manipulate and only needs to send its results back to the user, doing away with the overhead of communicating large amounts of data back and forth.

User-defined Function

A user-defined function is a routine that encapsulates useful logic for use in other queries. While views are limited to a single SELECT statement, user-defined functions can have multiple SELECT statements and provide more powerful logic which is not possible with views.

User defined functions have three main categories:

• Scalar-valued function - returns a scalar value such as an integer or a time-stamp  Can be used as column name in queries.

/*

SELECT [Test].[dbo].[Ufn_getfullname] ('HP') AS [Full Name]  

*/

CREATE FUNCTION Ufn_getfullname (@Name NVARCHAR(15))

returns VARCHAR(30)

AS

 BEGIN

 DECLARE @Return VARCHAR(30)

 SELECT @return = CASE @Name

WHEN 'HP' THEN 'Harish Patel'

WHEN 'PP' THEN 'Paresh Patel'

ELSE 'Unknown'

  END

 RETURN @return

 END

• Inline function - can contain a single SELECT statement.

/*

SELECT * from [Test].[dbo].[ufn_GetPersonDetails] ('1')   

*/

CREATE FUNCTION Ufn_GetPersonDetails (@PersonID VARCHAR(15))

returns TABLE

AS

RETURN

 SELECT * 

 FROM   Test.dbo.Persons A

 WHERE  A.P_Id = @PersonID

go

• Table-valued function - can contain any number of statements that populate the table variable to be returned. They become handy when you need to return a set of rows, but you can't enclose the logic for getting this row-set in a single SELECT statement.

/*

SELECT * FROM   dbo.Ufn_getpersondetailsbycity('Bochasan')

*/

CREATE FUNCTION dbo.Ufn_getpersondetailsbycity (@City VARCHAR(30))
returns @PersonsbyCityTable TABLE (
  [p_id]      [VARCHAR] (10),
  [lastname]  [VARCHAR] (25),
  [firstname] [VARCHAR] (25),
  [address]   [VARCHAR] (50),
  [city]      [VARCHAR] (30))
AS
  BEGIN
      INSERT INTO @PersonsbyCityTable
      SELECT [p_id],
             [lastname],
             [firstname],
             [address],
             [city]
      FROM   [Test].[dbo].[persons]
      WHERE  city = @City

      DECLARE @cnt INT

      SELECT @cnt = Count(*)
      FROM   @PersonsbyCityTable

      IF @cnt = 0
        INSERT INTO @PersonsbyCityTable
                    ([p_id],
                     [lastname],
                     [firstname],
                     [address],
                     [city])
        VALUES      ('',
                     'No Person Details Found',
                     'No Person Details Found',
                     '',
                     '' )

      RETURN
  END

go