What are the differences between Stored Procedures and Functions within Sql Server?

Asked

Viewed 469 times

2

This is a question in a certain academic way, but I have come up with the question. What are the differences between one and the other?

What I should consider to choose each case in certain scenarios?

Thank you.

sql sql-server

1 answer

4


Stored Procedure are objects that are previously compiled by SQL and whenever called will be executed from their "pre-compiled" code. But the Function function is compiled and executed every time it is called. Let’s see some differences between these two ways of processing data within our database.

The basic difference

  • Function must return a value, but in stored the return is optional.
  • Functions can only have input parameters. Storeds can have input/ output parameters.
  • Functions can be called from within storeds, storeds cannot can be called from functions.

Advanced differences

  • Procedures cannot be used in a SELECT instruction while that Function can be incorporated into a SELECT statement.

  • Procedures cannot be called and/or used in SQL anywhere in WHERE/HAVING/SELECT, while Function can be.

  • The most important feature of storeds procedures in relation to functions is the retention and reuse of the execution plan, while in the case of the function that will be compiled each time it is executed.

  • Functions that return tables can be treated as another data set. This means that we can use them in associations (JOINS) with other tables.

  • Exceptions can be handled by Try-catch block in a storeds while Try-catch block cannot be used in a Function.

  • We can use Transaction Management in a stored, not in a Function.

I hope I’ve helped.

Source: Codex Simplex

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.