Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • SQL Server : Comparison of stored procedure and UDF

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 235
    Comment on it

    Stored Procedures are pre-compile database objects which are compiled for first time and execution plan is saved for further use. However UDF is compiled and executed every time when it is invoked.This article provides a detailed comparison of stored procedures and UDF's.

    1) Stored Procedures can have both input and output parameters whereas UDF's have only input parameters.

    2) Stored Procedures can do DML operations whereas UDF's can only do a SELECT.

    3) Stored Procedures can call a UDF but a UDF cannot call a stored Procedures.

    4) Stored Procedures may return value(optional) but UDF's must return a value.

    5) Functions that return tables can be treated as another rowset which can be further used in JOINs with other tables.Same can not be done with Stored Procedures.

    6) Stored Procedure can use try-catch block for handling exceptions whereas try-catch block cannot be used in a UDF's.

    7) Stored Procedures can use transactions whereas we can't use them in UDF's

    Hope the above comparison clarifies the differences between Stored Procedures and UDF's.

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: