Tutorialsteacher

Follow Us

Articles
  • C#
  • C# OOP
  • ASP.NET Core
  • ASP.NET MVC
  • LINQ
  • Inversion of Control (IoC)
  • Web API
  • JavaScript
  • TypeScript
  • jQuery
  • Angular 11
  • Node.js
  • D3.js
  • Sass
  • Python
  • Go lang
  • HTTPS (SSL)
  • Regex
  • SQL
  • SQL Server
  • PostgreSQL
  • MongoDB
Entity Framework Extensions - Boost EF Core 9
  Bulk Insert
  Bulk Delete
  Bulk Update
  Bulk Merge
  • All
  • C#
  • MVC
  • Web API
  • Azure
  • IIS
  • JavaScript
  • Angular
  • Node.js
  • Java
  • Python
  • SQL Server
  • SEO
  • Entrepreneur
  • Productivity

Difference between Functions and Stored Procedures in SQL Server

The following table lists the difference between Functions and Stored Procedures in SQL Server database.

FunctionStored Procedure
Always returns a single value; either scalar or a table.Can return zero, single or multiple values.
Functions are compiled and executed at run time.Stored procedures are stored in parsed and compiled state in the database.
Only Select statements. DML statements like update & insert are not allowed.Can perform any operation on database objects including select and DML statements.
Allows only input parameters. Does not allow output parameters.Allows both input and output parameters
Does not allow the use of Try…Catch blocks for exception handling.Allows use of Try…Catch blocks for exception handling.
Cannot have transactions within a function.Can have transactions within a stored procedure.
Cannot call a stored procedure from a function.Can call a function from a stored procedure.
Temporary tables cannot be used within a function. Only table variables can be used.Both table variables and temporary tables can be used.
Functions can be called from a Select statement.Stored procedures cannot be called from a Select/Where or Having statements. Execute statement has to be used to execute a stored procedure.
Functions can be used in JOIN clauses.Stored procedures cannot be used in JOIN clauses
TUTORIALSTEACHER.COM

TutorialsTeacher.com is your authoritative source for comprehensive technologies tutorials, tailored to guide you through mastering various web and other technologies through a step-by-step approach.

Our content helps you to learn technologies easily and quickly for learners of all levels. By accessing this platform, you acknowledge that you have reviewed and consented to abide by our Terms of Use and Privacy Policy, designed to safeguard your experience and privacy rights.

[email protected]

ABOUT USTERMS OF USEPRIVACY POLICY
copywrite-symbol

2024 TutorialsTeacher.com. (v 1.2) All Rights Reserved.