Function vs. Stored Procedure in SQL Server

The Function has only input parameter.

The Store procedure has input and output parameter.

The Function always returns a value.

The stored procedure is optional. It’s May or may not return a value.

The Function utilised only SELECT command.

The Store procedure utilised DDL (Data Definition Language), DML (Data Manipulation Language) command.

DDL- CREATE, ALTER, DROP

DML- SELECT, INSERT, UPDATE, DELETE

You cannot use try catch block in Function.

You can use try catch block in Store procedure.

You cannot use transactions in Function.

You can use transactions in Store procedure.

You cannot call Store procedure inside Function.

You can call Function inside Store procedure.

The Function can be used in Select statement.

The Store procedure can’t use in Select statement.

The Function called using SELECT.

The Store procedure called using EXEC or EXECUTE.