Difference between Stored Procedure and Function in SQL

In SQL, Stored Procedures and Functions are both used to encapsulate reusable logic, but they serve different purposes and have key differences in how they are used, structured, and executed. Understanding these differences is essential for designing efficient and maintainable database systems.

What is a Stored Procedure?

A Stored Procedure is a precompiled collection of one or more SQL statements that perform a specific task. It can accept input parameters, perform operations such as INSERT, UPDATE, DELETE, and SELECT, and can also return output parameters or a result set.

Key Features of Stored Procedures:

• Can perform DML (Data Manipulation Language) operations.

• Can return multiple values via output parameters.

• Can call other procedures and functions.

• Can handle exceptions using TRY...CATCH.

• Execution does not return a value directly (unless using output parameters).

What is a Function?

A Function in SQL is a database object that accepts parameters, performs actions (usually calculations or transformations), and returns a single value. Unlike stored procedures, functions must return a value.

Key Features of Functions:

• Must return a value (scalar or table).

• Cannot perform INSERT, UPDATE, DELETE operations (in most DBMS).

• Cannot use transactions (COMMIT/ROLLBACK).

• Cannot call procedures but can call other functions.

• Primarily used for computations and data transformation.
  
  Watch my YouTube on this topic:-

 

How to create and execute stored procedure in SQL server ?

 Hi guys, in this blog post we will see how to create stored procedure in SQL and how to execute it. This blog post is useful for SQL server beginners. Let's see it practically.

Stored procedure is set of SQL statements which are created and stored on server, so that we can reuse them.

Syntax for creating stored procedure:-

CREATE PROC <Procedure_Name> 

-- Add the parameters for the stored procedure here

<@Param1> <Datatype_For_Param1> = <Default_Value_For_Param1>

AS

BEGIN

SET NOCOUNT ON;

         -- SQL statements for procedure here

SELECT  COLUMN_NAME1,COLUMN_NAME2  FROM TABLE_NAME

         Where COLUMN_NAME1=@Param1

END

For e.g. We have table with name tblEmployee. We will create stored procedure which will retrieve data from table. 

Please follow below steps to create stored procedure:-

1) Login into SQL server using username and password.

2) Select database in object explorer and click on new query.

3) Use syntax for creating stored procedure as shown below:-

4)After writing query click on execute so, stored procedure will be created in SQL server.

Create proc usp_GetEmployeeDetails

@EmployeeId int

As

Begin

Select EmployeeFirstName+' '+EmployeeLastName as [Employee Name] 

from tblEmployee 

where EmployeeId=@EmployeeId 

End

Syntax for executing stored procedure in SQL server:-

exec Stored_Procedure_Name parameters

We can execute above stored procedure like this:-

Write below query and click on execute option

exec usp_GetEmployeeDetails 1

Refer below screenshot: