SQL Server Stored Procedure VS User Defined Functions


This blogpost explains stored procedure, functions and their features is. It also describes difference between stored proc and user defined functions.


Stored Procedure

A Stored Procedure is a pre compiled object stored in 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 CREATE Procedure statement is executed, server compiles 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 Procedure is stored in sysobjects table, and 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, perform an action and return 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

User defined function

A Procedure may or may not return a value.

It is mandatory for a UDF to return a value.

A Procedure is compiled once and gets executed whenever it is called.

A UDF is compiled every time it is used.

A Function can be called from a Procedure.

A Procedure cannot be called from a UDF.

In a Procedure we can use SELECT, INSERT, UPDATE and DELETE.

In a Function we can only use SELECT statement.

In a Procedure to handle the exception we can use TRY-CATCH block.

A TRY-CATCH block cannot be used in a UDF.

While writing a Procedure we can use temporary variables, tables and CTE.

A UDF does not allow temporary tables.

A Procedure can have Input or Output parameters.

A UDF can only have Input parameters


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

    sql server select all rows

    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'
                    

    SQL server create and execute stored procedure


  • With Output Parameter

    Below given Procedure have 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 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'
                    

    sql execute stored procedure with output parameter


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 
      
                              

      sql execute scalar user defined function

    2. By using Function in Select Statement
          SELECT dbo.FirstLetter_Upper(EmpFirstName) AS FirstName
          FROM EmployeeDetails
          WHERE EmpID = 1001
                              

      sql execute udf function in select statement

    3. Table Valued function

      Creating Table Valued UDF

      This function returns table data from EmployeeDetails table by considering Department as a parameter, i.e. all employees for 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')
                              

      sql execute table valued function

      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)
                              

Download Source code

Speak your mind :
Leave a comment for this article on dotnetbloogers.com