Skip to main content

Difference between Stored Procedure and Function in SQL Server

Difference between Stored Procedure and Function in SQL Server

Stored Procedures are pre-compile objects which are compiled for first time and its compiled format is saved which executes (compiled code) whenever it is called. But Function is compiled and executed every time when it is called. For more about stored procedure and function refer the articles Different types of Stored Procedure and Different types of Function.

Basic Difference

1.       Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values).

2.       Functions can have only input parameters for it whereas Procedures can have input/output parameters .

3.       Function takes one input parameter it is mandatory but Stored Procedure may take o to n input parameters..

4.       Functions can be called from Procedure whereas Procedures cannot be called from Function.

Advance Difference

1.       Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.

2.       Procedures can not be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.

3.       Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.

4.       Functions that return tables can be treated as another rowset. This can be used in JOINs with other tables.

5.       Inline Function can be though of as views that take parameters and can be used in JOINs and other Rowset operations.

6.       Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.

7.       We can go for Transaction Management in Procedure whereas we can't go in Function.




Labels:

Comments

Post a Comment

Popular posts from this blog

.NET Core API With Dapper, Repository And UnitOfWork

  Introduction In this tutorial, you will use ASP.NET Core Web API to create a web API that returns a list of brands. This article focuses on creating web API using dapper, repository pattern, and UnitOfWork pattern in .NET 8. I will guide you through the steps by step to create web API. HTTP is not just for serving up web pages. HTTP is also a powerful platform for building APIs that expose services and data. HTTP is simple, flexible, and ubiquitous. Almost any platform that you can think of has an HTTP library, so HTTP services can reach a broad range of clients, including browsers, mobile devices, and traditional desktop applications. ASP.NET Core Web API is a framework for building web APIs on top of the .NET Framework. Creating a New Project in Visual Studio Launch Visual Studio and select Create a New Project. Creating a Core Web API Start Visual Studio and choose Create a new project. In the Create a new project dialog, select ASP.NET Core Web API Click Next. Configuring You...
Post a Comment
Read more

.Net framework interview questions

What is the .NET Framework? The .NET Framework is a set of technologies that form an integral part of the .NET Platform. It is Microsoft’s managed code programming model for building applications. The .NET Framework has two main components: Common Language Runtime (CLR): The CLR is one of the foundation in the .NET framework and provides a common set of services for applications developed on Microsoft .Net Technologies. .NET Framework class library: The .NET framework class library is a collection of reusable types and exposes features of the runtime. It contains of a set of classes that is used to access common functionality. What is CTS (Common Type System)? The common type system (CTS) defines how types are declared, used, and managed in the runtime, and is also an important part of the runtime’s support for cross-language integration. The common type system performs the following functions: ·          Establishes a framework that...
Post a Comment
Read more

Move Selected Gridview Rows to Another Gridview in Asp.net

Introduction :  Here I will explain how to move or transfer selected checkbox gridview rows to another  grid v iew in asp.net using c#, vb.net or copy one gridview row to another gridview in asp.net using c#, vb.net. Description :  In previous posts I explained Display images in gridview from database in asp.net, Enable/Disable checkbox in gridview based on condition,  asp.net gridview examples  and  bind data to textbox control in gridview ,  Bind data to dropdownlist  in gridview  and many articles relating to  asp.net,  c#, vb.net, c#,vb.net. Now I will explain how to move selected rows from one gridview to another gridview in asp.netusing c#, vb.net. To implement this first we need to write the code in aspx page like this  < html   xmlns ="http://www.w3.org/1999/xhtml"> < head > ...
Post a Comment
Read more