You must Sign In to post a response.
  • Category: SQL Server

    What is the difference between stored procedure and functions

    what is the difference between stored procedure and functions....
  • #743896
    There are many differences but coming to the Basic Difference between them is Function must return a value but in Stored Procedure it is optional.Function takes one input parameter it is mandatory but Stored Procedure may or may not take values.
    Functions can have only input parameters for it whereas Procedures can have input/output parameters .Functions can be called from Procedure whereas Procedures cannot be called from Function.
    Procedure allows all CRUD operations and transaction management inside whereas Function allows only SELECT statement in it. Function can be included in a SELECT statement in anywhere in the WHERE/HAVING/SELECT where as SP is not.Functions that return tables can be treated as another rowset. This can be used in JOINs with other tables.Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.

  • #743899
    Hi Rajanikant


    Basic Difference :-

    1. Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values).

    2. Functions can have only input parameters for it whereas Procedures can have input/output parameters .

    3. Function takes one input parameter it is mandatory but Stored Procedure may take o to n input parameters.

    4. Functions can be called from Procedure whereas Procedures cannot be called from Function.

    Advance Difference:-

    1. Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.

    2. Procedures can not be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.

    3. Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.

    4. Functions that return tables can be treated as another rowset. This can be used in JOINs with other tables.
    5. Inline Function can be though of as views that take parameters and can be used in JOINs and other Rowset operations.

    6. Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.

    7. We can go for Transaction Management in Procedure whereas we can't go in Function.

    Regards
    -----------------
    Sudeep Bhawsar

  • #743910
    Hi,

    There are no.of differences are there between function and stored procedure.

    StoredProcedure:

    1) SP can call another SP inside of that.
    2) SP return value as well as Table
    3) function can call inside SP.
    4) Returing value is optional.

    Functions:

    1) function return a value.
    2) inside funciton we can't able to call SP.
    3) It must return a value.

    For more information or other new points better to search in google, you may got no.of points out of this.

    Hope this will help you...

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #743923
    Hai Rajanikanth,
    This is also a very generic question and its better to search in our forum first to get the answers.
    Anyway, The functions in SQL Server are a database object which is used to perform a certain activity by encapsulating its logic.
    We can encapsulate logic which is used frequently in our database and make a function so that whenever we need that activity, instead of write the whole procedure again, we can simply call a function to do that activity.
    Functions always returns a value but the stored procedure may or may not. Its all depends on the stored procedure behavior to return the value if the out parameter is defined.
    Function can take only input parameter while the stored procedure can take both input and output parameters.
    We can call functions inside the stored procedure but we cant call stored procedure inside the functions.
    Hope it will be helpful to you.

    Regards,
    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
    pawansoftit@gmail.com

  • #743925
    hi,

    this is the mostly asked interview question and the most important difference is that
    Stored procdure may or may not return any value.
    While in case of function it must return value.

    Hope this helps you

    Regards
    Shalini

  • #744044
    Hi..


    Go through he following mentioned links.. This will help u lot..

    Links:

    1.http://blog.sqlauthority.com/2011/06/04/sql-server-question-to-you-when-to-use-function-and-when-to-use-stored-procedure/

    2.http://www.codeproject.com/Questions/469619/difference-between-stored-procedure-and-function-i

    3.http://www.c-sharpcorner.com/UploadFile/skumaar_mca/differences-between-procedures-and-functions/


  • Sign In to post your comments