When to use stored procedure and when to use function?

Question

Syed Shanu · Answer

Function can be used in Select query.For example you want to get some name by passing some code and need to use this fucntionality in more then one select query. In that case you can create a Function and call the function in your select query. You can also call a function from Stored procedure select ,Insert and update query to return the result value to your procedure. Stored procedure can be used to execute more then one query for example you can have select,Insert ,Update all in one procedure. For example if you want to insert a Item name to a table but you need to check for the item name already exist or not .In this case you need both Selct and then Insert query.In your SP you can check for item name exits and then insert.

Pankaj Kumar Choudhary · Answer

Hello @Jagdev it depend upon your requirement and your choice if you are confuse that When to use stored procedure and when to use function? then you should have a knowledge about diff b/w both that are followings 1) Procedure can return zero or n values whereas function can return one value which is mandatory 2) Procedures can have input, output parameters for it whereas functions can have only input parameters. 3) Procedure allows select as well as DML(INSERT/UPDATE/DELETE) statements in it whereas function allows only select statement in it. 4) Functions can be called from procedure whereas procedures cannot be called from function. 5) Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function. 6) We can go for transaction management in procedure whereas we can't go in function. 7) Procedures cannot be utilized in a select statement whereas function can be embedded in a select statement.

Syed Shanu · Answer

Function is something we can write once and use it in many place .For example if you want to find a leap year and in more then one report or in page for different SP you want to find the leap Year day and produce the result in select query.In that case you can wrtie a function to find a Leap Year day for a year and return the date.You can use this result in any no of SP.You no need to write the same query again and again to find the result and display in SP.Write once use it many time and in many place as per your requirement.

Jagdev Mishra · Answer

Dear all. If we can do everything with procedure and it is secure as well as. So what is need of function. 1. We can select with procedure also. So whats is need of function? 2. Or when we need to select someting then we have to use function other wise use procedure?

Upendra Pratap Shahi · Answer

Read below article.. http://blog.sqlauthority.com/2011/06/04/sql-server-question-to-you-when-to-use-function-and-when-to-use-stored-procedure/ http://blog.sqlauthority.com/2011/06/02/sql-server-puzzle-statistics-are-not-updated-but-are-created-once/ http://www.c-sharpcorner.com/UploadFile/rohatash/calling-a-function-from-a-stored-procedure-in-sql-server-201/ http://www.aspdotnet-suresh.com/2012/10/sql-server-functions-vs-stored.html function is used on that scenario where we want to use code reuseability. e.g- If we have 3 columns firstname, middlename,lastname and there values are 'upendra','pratap','shahi' and we want to concatenate this as a single value like 'upendra pratap shahi' then we should write the function for this and get column vale as need We can work on column level for all values. If this is satisfied you kindly mark the answer as Accepted Answer

Nilesh Jadav · Answer

Hello Jagdev Actually it is not like when to use store procedure but if you are using then it would be the best for your applications. 1) It protect your table data from intruders. 2) Using Store Procedures you are separating your database from the coding part, and it is good thing. From that any one can understand the flow of your application. 3) It also used for making fewer calls to the database. Suppose you want to fire 4 sql query. If you are using Store Procedure you can do it by one sql statement. Hence not much disturbance to the Database. Function have this many advantages: 1) It Can be used with Select statement 2) It will not return output parameter but returns Table variables 3) You can join UDF 4) It Cannot be used to change server configuration 5) It Cannot be used with XML FOR clause 6) It Cannot have transaction within function

Khargesh Rajput · Answer

function is used to only return values stored procedure used to insert delete update all statements