Total Page Preview:   000000012420

What is Difference between Stored Procedure and Function in SQL Server

Stored Procedures:
Stored Procedures is pre-compile objects which are compiled for first time and its compiled format is saved which executes (compiled code) whenever it is called. 
 
Function:
Function is not pre-compiled object it will execute every time whenever it was called.
 
Difference between Stored Procedure and Function
  Stored Procedures Function
Basic Difference
1 Stored Procedure it is optional (Procedure can return zero or n values) Function must return single value
2 Stored Procedures can have input/output parameters Functions can have only input parameters
3 Functions can be called from Stored Procedure Stored Procedures cannot be called from Function
Advance Difference
4 Exception can be handled by try-catch block in a stored procedure Exception try-catch block cannot be used in a function
5

StoredProcedure allows SELECT as well as DML(INSERT/UPDATE/DELETE)

statement in it

Function allows only SELECT statement in it
6 Stored Procedures cannot be utilized in a select statement Function can be embedded in a select statement
7

Stored Procedures cannot be used in the SQL statements anywhere in the

WHERE/HAVING/SELECT section

Function can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT

section

8 We can go for Transaction Management  in Stored Procedure. We cannot go for Transaction Management in Function
9 Stored Procedure can fire Triggers Function can not fire Triggers.
10 Stored Procedure can create Table variable but can not return table variable Function can create Table variable and return table variable.
11 Print command can be use in stored procedure Function can not be use print command.
12 Store procedure can execute Dynamic SQL Function cant not be execute Dynamic SQL
13 DML and DDL operation can be performed in SP

DML and DDL operatoin can be performed in function  but we can use only select statement

and Insert statement to insert row into table variable

14 Temporary table can be accessed in stored procedure. Temporary Table can be created in SP Temporary Table Can not be accessed in Function. We can not create Temporary in Function

 

Basic Difference:

Basic Difference Store Procedure And Function
Advance Difference
Advance Difference Store Procedure And Function

Thanks !

 

 

Thank You

About Author

Brijesh Kumar

Database Developer

I have more then 6 years Experience in Microsoft Technologies - SQL Server Database, ETL Azure Cloud - Azure SQL Database, CosmosDB, Azure Data Factory, PowerBI, Web Job, Azure Function, Azure Storage, Web Apps, Powershall and Database Migration On-Premise to Azure Cloud.
LinkedIn : https://www.linkedin.com



Comments

Junkins
07-Jun-2021
If you ԝant tօ get much frоm tһis piece of writing thеn yoᥙ have to apply thesе methods tο your won webpage.

                           
                           

                           

                           

Recent Posts

How to get the alternate rows or records from table in sql server
07-Jun-2017
Data Warehouse Three Tier Architecture
20-Aug-2017
SQL Server - Fragmentation on azure sql database
07-Oct-2018
Limitations of Azure SQL Database
19-Sep-2019
PIVOT and UNPIVOT in SQL Server with Example
29-Jun-2017
SQL Server - Find all index with key columns on Azure SQL Database
02-Oct-2018
SQL Server - Find unused indexes on azure SQL database scripts
02-Oct-2018
Find Resource usage in Azure SQL Database using script or T-SQL
07-Oct-2018
How to Set client IP Address using the firewall security in Azure SQL Using Portal
29-Jun-2017
What is a Trigger and type of trigger in SQL server?
10-Sep-2021
What is Table Variable? Explain its advantages and disadvantages?
10-Sep-2021
Top 60 SQL Server Interview Questions Answers for freshers and experienced
17-Sep-2021
Cloud Data Engineer SQL Server Interview Questions Answers for freshers and experienced
31-Aug-2021
Top 16 performance query optimization techniques in SQL Database?
16-Sep-2021
Data warehouse Interview Questions Answers for freshers and experienced
16-Sep-2021
What are the types of User Define Function and difference between a nested UDF and recursive UDF in SQL server?
10-Sep-2021
What are the different types of Error Handling in SQL Server?
11-Sep-2021
What are the differences between OLTP and OLAP?
07-Sep-2021
How many types of dimension tables available in data warehouse?
16-Sep-2021
What are characteristics and advantages of stored procedure?
10-Sep-2021

Facebook User: