Diference Between Stored Procedures And Functions

Introduction

 

Both stored procedure and functions, are database objects that contain a set of SQL statements to complete a specific task. But there are also some differences among them. We will discuss their differences and their usage in this article.

 

Stored Procedures

 

Stored procedures are pre-compiled objects which are compiled for the first time and its compiled format is saved. It will execute whenever it is called. If you think of a query that you write over and over again, instead of writing that query each time you save your query as a stored procedure and whenever you want that query to be executed you just call saved store procedure. You can also pass parameters to the stored procedure. So, it becomes more flexible and you can do what your parameter say in your stored procedure.

 

Stored Procedures can also improve performance. Many tasks are implemented as a series of SQL statements. The logic applied to result of first SQL statements determine which subsequent SQL statements are to be executed. If these SQL statements and conditional logic are written into a Stored procedure, they become part of a single execution plan on the server. All of your work will be done on the Server which makes it faster.

 

Advantages of Stored Procedures

 

Performance

 

Stored procedures are compiled once and stored in executable form, so procedure calls are quick and efficient. Executable code is automatically cached and shared among users. This lowers memory requirements and makes application fast.

 

Productivity and Ease of Use

 

By passing parameters to stored procedures, you can handle your logic in stored procedures. You can use the IDE of your choice and then you can deploy them on any tier of network architecture. Moreover, they can be called by programmatic interfaces and development tools.

 

Security Controls

 

You can grant user permissions to execute a Stored Procedure independently of underlying table permissions.

 

Functions

 

Like functions in programming languages, SQL Server user-defined functions are routines that accept parameters, perform an action such as a complex calculation, and return the result of that action as a value. The return value can either be a single scalar value or a result set. A function is compiled and executed every time whenever it is called. A function must return a value and cannot modify the data received as parameters.

 

There are two types of Functions,

• Built-in functions
  Operate as defined in the Transact-SQL Reference and cannot be modified. The functions can be referenced only in Transact-SQL statements using the syntax defined in the Transact-SQL Reference.
  
  
• User-Defined Functions
  By using the CREATE FUNCTION statement, users can write their own Transact-SQL functions according to their specific need. User-defined need zero or more input parameters and return a single data value.

Advantages of Functions

 

Modular Programming

 

You can create a function once, store it in your database and use it whenever you need.

 

Faster Execution

 

Similar to Stored Procedures, Transact-SQL User-Defined Functions reduce the compilation cost of Transact-SQL code by caching the plans and reusing them for repeated executions. This makes it faster.

 

Difference between Store procedure and functions

1. The function must return a value, but in Stored procedure it is optional. Even a stored procedure can return zero or n values.
   
2. Functions can be called from Stored procedures while a Stored procedure cannot be called from a function.
   
3. The procedure allows to write INSERT, UPDATE, DELETE statements with SELECT statement while function only allows SELECT statement.
   
4. Procedures cannot be utilized in a SELECT statement while a function can be embedded in a SELECT statement.
   
5. Stored procedures cannot be used in SQL statements like WHERE/HAVING/SELECT statement whereas functions can be used.
   
6. We can use transactions in Stored procedures while transactions cannot be used in functions.
   

Code Examples

 

Printing Hello World in stored procedure vs. in function.

 

In the stored procedure,

CREATE PROCEDURE HelloWorldprocedure
AS
PRINT 'Hello World'

Executing stored procedure

exec HelloWorldprocedure

In function,

CREATE FUNCTION dbo.helloworldfunction()
RETURNS varchar(20)
AS
BEGIN
     RETURN 'Hello world'
END

Using functions,

select dbo.helloworldfunction() as regards

Using variable in Stored Priocedure vs. in Function

 

In the stored procedure,

CREATE PROCEDURE CONVERTCELSIUSTOFAHRENHEIT
@celsius real
as
select @celsius*1.8+32 as Fahrenheit

Executing a stored procedure,

exec CONVERTCELSIUSTOFAHRENHEIT 0

In function,

CREATE FUNCTION dbo.f_celsiustofahrenheit(@celcius real)
RETURNS real
AS
BEGIN
RETURN  @celcius*1.8+32
END

Using function,

select dbo.f_celsiustofahrenheit(0) as fahrenheit

In this article, we discussed what stored procedures and functions are, what are their differences, and how they are used.

 

Happy coding!!!