Differences between Functions and Stored Procedures in SQL Server

Differences between Functions and Stored Procedures in SQL Server

Stored Procedure:

·         Stored Procedures are a batch of SQL statements that can be executed in a couple of ways.

·         Stored Procedure is a Set precompiled SQL statements that used to perform a special Task

·         Stored procedure is a group of SQL statements that has been created and stored in the database. 

·         Store Procedure use to retrieve data, modify data, and delete data in Database table.

Function:

·         User-defined function is a Transact-SQL or common language run time (CLR) routine that accepts parameters, performs an action, such as a complex calculation, and returns the result of that action as a value. The return value can either be a scalar (single) value or a table. 

·         Function is a database object in SQL Server. Basically it is a set of SQL statements that accepts only input parameters, perform actions and return the result. Function can return only single value or a table. 

·         Microsoft has introduced the concept of User-Defined Functions that allow you to define your own T-SQL functions that can accept zero or more parameters and return a single scalar data value or a table data type.

Difference between them (Functions and Stored Procedures)

Stored Procedure	Function
Stored procedures may or may not return values	Function always return value
Stored procedure will be used for perform specific tasks	Normally functions will be used for computing value
Stored procedure cannot be used in the select/where/having clause	Function can be called from select/where/having clause
Stored procedure can run independently. It can be executed using EXECUTE or EXEC command	Function cannot run independently
From SQL server 2005 onwards, TRY CATCH statements can be used in the stored procedures.	Function cannot be used in the function. But we can use raise error function.
Stored procedure can call the user defined functions	Function cannot call the stored procedures.
Stored procedures can have input and output parameters.	Function can have only input parameters.
Stored procedures can have select and all DML operations.	Function can do only select operation.
Stored procedure can use transaction statements.	Function cannot have the transaction statements.
Stored procedures can use all the data types available in SQL server.	Function cannot use the ntext, image and timestamp data types as return type.
Stored procedures can create table variable and cannot return the table variable.	Function can create, update and delete the table variable. It can return table variable.
Stored procedure can have the dynamic SQL statement and which can be executed using sp_executesql statement.	Function cannot execute the sp_executesql statement.