SQL Server Stored Procedure VS User Defined Functions

Stored Procedure

A Stored Procedure is a pre compiled object stored in the database. In easy words, we can say it is a batch of code. Since batch is temporary in nature, we can save the code within a Stored Procedure so that we can use it multiple times by executing it.

When the CREATE Procedure statement is executed, the server compiles the stored procedure and save it as a database object. Compilation of code is done once and every time compiled code gets executed. The name of the Procedure is stored in sysobjects table, and the code that creates Procedure is stored in syscomments table.

Syntax to Create Stored Procedure

CREATE PROCEDURE <procedure_name<
AS
BEGIN
	<sql statements>
END

        

Syntax to execute existing stored procedure

EXEC <procedure_name> <parameter list seprated by comma>
        

User Defined Functions

We can also create functions to store a set of T-SQL statements permanently. These are called User Defined Functions (UDF). A UDF accepts a parameter, performs an action, and returns the result. A UDF can be scalar, which accepts a single parameter and returns a single data value of the type specified. Also, it can be table-valued which accepts a parameter and returns a table as an output.

Syntax to Create User Defined Functions

CREATE FUNCTION <function_name> (<@paramater_name>    <datatype>)
RETURNS <return_datatype>
AS
BEGIN
 	<DML statements>
    RETURN expression
END

        

A function can be called alone with name and parameter or it can be called inside a SELECT statement.

Syntax to execute UDF

    SELECT * FROM <Function_name>(Parameter)
        

OR

    SELECT field1, <function_name(parameter)> FROM table_name
        

For more details on User Defined see scalar and table valued user defined function.

Difference between Stored Procedure and User Defined function

Stored Procedure examples

• With Input Parameter

  Consider the table EmployeeDetails, we will write a Procedure which will take an input parameter ‘Department’ to show the output.

      SELECT * FROM EmployeeDetails
                  

  It generates below output

  

  Below is the procedure named ListofEmp. It returns the EmpID and Name of the employees on the basis of Department passed as a parameter.

  Creating a Procedure

      CREATE PROC ListofEMP @Department CHAR(50)
      AS
  
      BEGIN
  	    SELECT EmpID,EmpFirstName+' '+EmpLastName AS EmpName 
  	    FROM EmployeeDetails
  	    WHERE Department = @Department
      END
  
                  

  Executing a Stored Procedure

  EXEC ListofEMP 'IT'
                  

  

• With Output Parameter

  Below the given Procedure has one input parameter EmpID and two output parameter Department and EmpName. The Procedure will first check is the EmpID passed as a parameter exists, then the rest of the procedure will be executed.

  Creating Procedure

  CREATE PROC SingleEMP 
      @EmpID INT,
  	@Department CHAR(50) OUTPUT,
      @EmpName CHAR(100) OUTPUT
  AS
  BEGIN
  IF EXISTS (SELECT * from EmployeeDetails where EmpID = @EmpID)
  BEGIN
  	SELECT 
  	@Department = Department,
  	@EmpName = EmpFirstName+' '+EmpLastName 
  	FROM EmployeeDetails
  	WHERE EmpID = @EmpID
  	Return 0
  END
  ELSE
      RETURN 1    
  END
  
              

  Executing Procedure

      DECLARE @Department_output char(50), @EmpName_output char(100)
      
      EXEC SingleEMP 1001,
  	    @Department = @Department_output OUTPUT,
  	    @EmpName = @EmpName_output OUTPUT
  	    PRINT @Department_output
  	    PRINT @EmpName_output
  	    PRINT 'Execution complete'
                  

  

Example of User defined function

• Scalar function

  The below written UDF returns name passed a parameter in initial caps.

      CREATE FUNCTION dbo.FirstLetter_Upper (@Name varchar(100))
          RETURNS varchar(100)
      AS
      BEGIN
      RETURN
  	    (
      	    UPPER(LEFT(@Name,1))+LOWER(SUBSTRING(@Name,2,LEN(@Name)))
  	    )
      END
  
                  

  Executing a function

  This UDF can be executed in two ways.

  1. By only using Function Name

     DECLARE @Name VARCHAR(100)
     SET @Name = dbo.FirstLetter_Upper('bhAvana')
     PRINT @Name 
     
                             

     

  2. By using Function in Select Statement

     SELECT dbo.FirstLetter_Upper(EmpFirstName) AS FirstName
     FROM EmployeeDetails
     WHERE EmpID = 1001
                             

     

  3. Table Valued function

     Creating Table Valued UDF

     This function returns table data from the EmployeeDetails table by considering Department as a parameter, i.e. all employees for the specified department would be returned in the table format.

     CREATE FUNCTION dbo.Department_Wise (@department varchar(100))
     RETURNS TABLE
     AS
     RETURN
     	(
     	SELECT * 
     	FROM EmployeeDetails
     	WHERE Department = @department
     	)
     GO
     
     

     Executing a table valued function

         SELECT * FROM dbo.Department_Wise('IT')
                             

     

     Execute with JOIN

     You can INNER JOIN OR OUTER JOIN OR CROSS JOIN table valued function's output with any table or view.

         SELECT *
         FROM Employee AS a
         CROSS APPLY dbo.Department_Wise(a.department)