in which cases we use stored procedures and function?

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/

Hi,

Stored Procedure -

If you want perform some DML (Data Manipulation Language) operations on the table like Insert,Update 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.

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.