in which cases we use stored procedures and function?

Question

Let me know any one who know the answer.... In which scenario will use stored procedure and a function?

Ranjit Kanojia · Answer

Hi For deep knowledge please go given link:- http://blog.sqlauthority.com/2011/06/04/sql-server-question-to-you-when-to-use-function-and-when-to-use-stored-procedure/

Pawan Vaish · Answer

Hi Stored Procedure - If you want perform some DML (Data Manipulation Language) operations on the table like InsertUpdate etc. then use stored procedure. If you want to fetch data from database table you can use stored procedure. Stored Procedure are pre-compiled object in sql i.e after creating stored procedure when you call stored procedure next time its very fast in term of performance. Function - If you want to write some logic which takes some input (not mandatory) and give you output based on your input then you can use function. Its is reusable. Like SUM function of sql server. Hope this will help you.

Amit Singh · Answer

There's no magically performance boost from either one. If they contain the same code they'll have the same performance. I'd say keep it as a function unless or until you reach a limitation of functions - e.g. if you want to actually change database state. Functions are more composable - you can call them directly from within other statements such as SELECT - whereas stored procedures have to be explicitly called.