Skip to main content

Main differences between Stored procedures and Functions in Sql Server ?


Stored Procedure:
A stored procedure is a pre-compiled group of Transact-SQL statements .We can say a stored procedure is a prepared SQL code that we save so that we can reuse the code over and over again. If a repetitive T-SQL task has to be executed within an application, then the best way for it is to create stored procedure.
It is always recommended to create Stored Procedure instead of writing Inline queries so that we can just call the Stored Procedures whenever required instead of writing Inline queries again and again each time.
You can also pass parameters to the stored procedure, so depending on what the need is the stored procedure can act accordingly based on the parameter values that were passed to it.
Function:
Function in Sql Server is a Transact-SQL or common language runtime (CLR) routine that takes
parameters, performs an action, and returns the result of that action as a value. The return value can either be a scalar (single) value or a table.
Difference between Stored procedure and Function
1. Function can return only 1 value whereas Stored Procedure can return many values(maximum 1024)
2. Functions can have only input parameters for it whereas Stored Procedures can have input/output parameters.
3. Function takes one input parameter which is mandatory but Stored Procedure may take Zero to n input parameters.
4. Functions can be used in a select statement where as Stored Procedures cannot.
5. Functions can be called from Stored Procedure whereas Stored Procedures cannot be called from Function.
6. Stored procedures are called independently, using the EXEC command, while functions are called from within another SQL statement.
7. Functions must always return a value (either a scalar value or a table). Stored procedures may return a scalar value, a table value or nothing at all.
8. Stored Procedure can be used to read and modify data but function can only read data.
9. Stored Procedure allows SELECT as well as DML (Data Manipulation Language) statements like INSERT/UPDATE/DELETE in it whereas Function allows only SELECT statement in it.
10. Procedures cannot be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.
11. Stored Procedures cannot be used as an inline with a select statement while Functions can.
12. Stored procedures are compiled for first time and compiled format is saved and executes compiled code whenever it is called. But Function is compiled and executed every time it is called.
13. Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.
14. Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.
15. Stored Procedure allows Transaction Management whereas Function doesn’t.
16. Stored procedures can be used to change server configuration settings (in terms of security-e.g. setting granular permissions of user rights) whereas function can't be used for this
17. The Stored Procedures can perform certain tasks in the database by using insert, delete, update and create commands but in Function you can’t perform use these commands.
18. Normally the Stored procedures are used to process certain task but the Functions are used to compute the values i.e. we can pass some value as input and then it perform some task on the passed value and return output.
19. Stored Procedures can be executed using Execute or Exec command where as Functions can run as an executable file.
20. Functions can be used as user defined data types in create table but procedures cannot.
Labels:

Comments

Post a Comment

Popular posts from this blog

What is cookie? Advantages and disadvantages of cookies?

What is cookie? A cookie is a small piece of text file stored on user's computer in the form of name-value pair. Cookies are used by websites to keep track of visitors e.g. to keep user information like username etc. If any web application using cookies, Server send cookies and client browser will store it. The browser then returns the cookie to the server at the next time the page is requested. The most common example of using a cookie is to store User information, User preferences, Password Remember Option etc.It is also one of the common and mostly asked interview questions. Some facts about Cookie Here are a few facts to know about cookies: · Cookies are domain specific i.e. a domain cannot read or write to a cookie created by another domain. This is done by the browser for security purpose. · Cookies are browser specific. Each browser stores the cookies in a different location. The cookies are browser specific and so a cookie created in one browser(e.g in Google Chrome
2 comments
Read more

Code First Getting Started

In this tutorial let us create a simple application to demonstrate the use of entity framework using code first. We are using Visual Studio 2015 and entity framework 6.1.3. You can download Visual Studio community Edition . You should have the basic knowledge of .Net framework, C# and MS SQL Server. In this tutorial, we will create a simple application with a user class.  Our user class will have basic information like name and email address of the user. Create the Project Open Visual Studio. File ->New -> Project Select C# -> Select Console Application Name the application as “EFGettingStarted” Click on OK Install Entity Framework The next step is to install the Entity framework. This can be installed via nuget package console. Click on Tools->Nuget Package manager -> Package Manager Console and type the following command C# 1 2 3   install - package entityframework   This will install the late
1 comment
Read more

First, FirstOrDefault, Single, SingleOrDefault In C#

For people who are new to LINQ, it is difficult to understand the difference between First, FirstOrDefault, Single, SingleOrDefault. In this blog, I will explain what to use and when.     I will take a simple example to make you understand practically how these methods work.   Consider a class Employee with properties as Id, Name, and Department. class  Employee {    public   int  Id {  get ;  set ; }    public   string  Name {  get ;  set ; }    public   string  Department{  get ;  set ; } } I have a list of Employees: List<Employee> employeeList =  new  List<Employee>(){    new  Employee() { Id = 1, Name =  "Sunny" , Department =  "Technical"  },    new  Employee() { Id=2, Name= "Pinki" , Department = "HR" },    new  Employee() { Id=3, Name= "Tensy" , Department = "Finance" },    new  Employee() { Id=4, Name= "Bobby" , Department = "Technical" },    new
3 comments
Read more