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

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 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'
                    

    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 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')
                              

      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)
                              

Source code on Git hub Source Code on Github

Speak your mind
Please login to post your comment!