Sunday , 25 June 2017
Home » SQL » Stored Procedure vs Functions

Stored Procedure vs Functions

Stored Procedure vs Functions

Stored Procedure vs FunctionsIn this article I am going to show you the differences between a Function and a Stored Procedure in sql
server. They are both stored in the database server. First let me show you how you can create user defined
function and a stored procedure.

I am going to create a simple function that will add two numbers and give you the result (example: 2+2=4).

Create the function with the following code.

In order to use this function type the following

When you run the query above it’s going to calculate 3+7 and the result will be 10. To read more about functions click on the following link (Functions in SQL Server)

We have seen how to create user defined¬†function. Now let’s create a stored procedure as well and then see the differences.

To create stored procedure type the following.

You can run this procedure by right clicking on the procedure name in SQL Server Management Studio and click on ‘Execute Procedure’ or type the following.

If you don’t want to see the result, you don’t need to declare and use select statement.
If you want to see more about stored procedures click on the following link (Stored Procedures)

Differences between Functions and Stored Procedures

  • In a function you can run only a single code or query. In a stored procedures you can run multiple queries.
  • Functions must have a return value and stored procedures may or may not have a return value.
  • Transactions are not allowed inside functions. In stored procedures you can use transaction(s).
  • You can not call a stored procedure in a function. In stored procedure you can call function(s).
  • You can not use try catch inside function. In stored procedure you can use try catch to get error exceptions.
  • You can call functions with select statement. Stored procedures are called with ‘execute’ or ‘exec’ command.
  • You can not use DML in functions. In stored procedure you can DML (insert – update – delete).
  • You can not use output parameter in functions. Stored procedures accept output parameter.
  • You can not use temporary tables in functions. In stored procedures you can use temporary tables.

Check Also

What is a Stored Procedure

What is a Stored Procedure What is a Stored Procedure? Stored procedures are a group …

Leave a Reply

Your email address will not be published. Required fields are marked *