Difference between a stored procedure and function is a big and never ending debate. It has been observed that a stored procedure can do almost all the things that a function can do. Remember “All most all” not 100 percent. Many people suggests to write a stored proc instead of a function, but do not forget a function can work pretty well, when there is some calculation to be made or in some non deterministic operations. They both are made to solve different purposes in real time. So be realistic while choosing between a stored procedure and function. I have lined up few differences here, if you know any other differences then please feel free to leave it in comment section below.
|It can only be called by EXECUTE statement hence WHERE clause is not possible and can not be used in joins.||It can be called after SELECT statement and hence WHERE clause is possible only for table valued functions and can be called after joins.|
|It is not mandatory to call a stored procedure with its schema name.
Eg: exec usp_abc
|It is mandatory to call user defined scalar function with its schema name.
Eg: SELECT dbo.udf_xyz()
|All types of operations can be done like DDL, DML, TCL and DCL.||Only DML operations, and that to only SELECT statement on physical tables not insert, update, delete and Merge.|
|Value return is not mandatory.||Value return is mandatory.|
|It can return multiple result set at a time.||it can return only one result set at a time.|
|Dynamic query execution is possible.||Dynamic query execution is not possible.|
|Functions can be called inside a stored procedure.||Stored procs can not be called inside a function except few system automation stored procs like sp_OAMethod, sp_OADestroy etc.|
|Database maintenance tasks like BACKUP, RESTORE, REBUILD INDEX etc are possible inside a procedure.||Database maintenance tasks are not possible inside a function.|
|A stored proc can not be SCHEMABINDING.||A function can be SCHEMABINDING.|
|Can have OUTPUT parameters.||Can not have OUTPUT parameters.|
|Order of the parameters does not matter while calling a stored proc.||Order of the input parameters does matter while calling a function.|
|It is not mandatory to pass default input parameters into stored proc while executing.||It is compulsory to pass keyword DEFAULT for default parameters while executing.|
|Reuses the execution plan.||It compiles every time.|
|Error handling is possible.||Error handling is not possible.|