SQL Server Stored Procedures Vs User Defined Functions

What is the difference between SQL Server Stored Procedures and User Defined Functions (UDF)?




Following are the some major difference between Stored procedures and User Defined functions

Return a value

Stored procedure may or may not return a value while UDF function must return a value.

Support DML operations

Stored Procedure supports select statement as well as DML operations like we can use insert, update and delete statement  while UDF function does not support DML, allow only select statement.

Parameters

Stored Procedure supports both type of parameters input and output while UDF function supports only input parameter, does not support output parameter.

Try.. catch block

Store Procedure supports TRY.. CATCH block to handle an exception while UDF function does not support TRY..CATCH block.

 Use with Join clause

Stored procedure cannot be used in join clause as a result set while UDF function can be used in join clause.

Transactions

Stored procedure supports transactions while UDF function does not support transactions.

Execution

Stored procedure can be called through EXEC or EXECUTE statement, it cannot be called from select statement while function can be called from Select statement.

Use of Temporary tables

Stored procedure allows temporary tables while UDF Function does not allow temporary tables.

Calling UDF function/stored Procedure

Function can be called from stored procedure while stored procedure cannot be called from function.

Also Read..

Stored procedures

User Defined functions



SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More




 79 total views,  6 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.