Stored Procedure vs Function in SQL Server

Stored Procedure:- Stored procedure in sql server can be defined as the set of logically group of sql statement which are grouped to perform a specific task. 

Function:- Functions in programming languages are subroutines used to encapsulate frequently performed logic. Any code that must perform the logic incorporated in a function can call the function rather than having to repeat all of the function logic.

In the same way there are many differences between a stored procedure and function as below ,

Stored Procedure	Function
Can return output parameters	Can’t return parameters
Can be used to read and modify data	Can only read data, cannot modify the database
To run an SP Execute or Exec is used, cannot be used with SELECT statement	Can only be used with SELECT statement, JOINS & APPLY (CROSS & OUTER)
Cannot JOIN a SP in a SELECT statement	Can JOIN a UDF in a SELECT statement
Can use Table Variables as well as Temporary Tables inside an SP	Cannot use a Temporary Table, only Table Variables can be used
Can create and use Dynamic SQL	Cannot use a Dynamic SQL inside a UDF
Can use transactions inside (BEGIN TRANSACTION, COMMIT, ROLLBACK) an SP	Cannot use transactions inside a UDF
Support RAISEERROR and @@ERROR	No support for error
Can use used with XML FOR clause	Cannot be used with XML FOR clause
Can use a UDF inside a SP in SELECT statement	Cannot execute an SP inside a UDF
Cannot be used to create constraints while creating a table	Can be used to create Constraints while creating a table
Can execute all kinds of functions, be it deterministic or non-deterministic	Cannot execute some non-deterministic built-in functions, like GETDATE()
Returns multiple result sets	Can’t returns multiple result sets
Syntax: CREATE PROCEDURE usp_MyProcedure AS BEGIN -- SELECT * FROM <Table> END GO	Syntax: CREATE FUNCTION dbo.FunctionName     (parameter_name data_type [=default] [, n]) RETURNS scalar_data_type [WITH function_option] AS BEGIN -- Function_body -- RETURN scalar_expression END

 

Keep learning,,,,,,