Varun Setia
What is Difference Between Function and Stored Procedure in SQL Server ?

What is Difference Between Function and Stored Procedure in SQL Server ? Compare the both and when to use which ?

By Varun Setia in .NET on Aug 03 2020
  • Sunil Hurkat
    Sep, 2020 1

    Store procedure is collection of queries and its one time compiled and compiled format is saved, which executes (compiled code) whenever it is called. where function is compiled and executed every time. And another difference function must return a value where as sp(store procedure ) not Its optional. Function have only input parameters whereas Procedures both input or output parameters. Functions can be called from Procedure whereas Procedures cannot be called from a Function.

    • 1
  • Surendra Badireddy
    Aug, 2020 7

    Stored Procedure: Stored Procedures are pre-compiled objects which are compiled for the first time and its compiled format is saved, which executes (compiled code) whenever it is called.

    Function: A function is compiled and executed every time whenever it is called. A function must return a value and cannot modify the data received as parameters.

    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.

    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.

    • 1

Most Popular Job Functions


MOST LIKED QUESTIONS

var $j = jQuery.noConflict();