What is the difference between sp and function

Before PostgreSQL version 11, stored procedures were effectively functions that didn't return data. But now there is a way to explicitly declare stored procedures, which also has the advantage of being able to open a new transaction, and they are now called differently too.

Before version 11, to declare a stored procedure we would just use a function with a return type of void, because we aren't looking to get any data back:. This stored procedure does not return any result, although will show any errors that occurred during its invocation.

From these examples, you can see that you can use functions to update and retrieve data, or to just perform a procedure, but now we also have procedures, which are designed specifically with performing a set of actions in mind. Functions come in even more flavors though. We have the regular kind, which are used in queries, but we also have some additional ones, which we'll get to shortly. Function parameters can be referenced using either positional references or named references.

For named references, you can just use the name of the parameter. You can also call the function and specifically set the parameters in whichever order you like using named notation:. This means we could use named notation, not specifying the "active" parameter, and it would automatically set that value to "true. There is also the OUT parameter, which is a way to produce an output that returns those fields in the result.

And INOUT, which we use to define a parameter that accepts input, but will also be included in the output. Stored Procedures can also improve performance. Many tasks are implemented as a series of SQL statements. Conditional logic applied to the results of the first SQL statements determine which subsequent SQL statements are executed. If these SQL statements and conditional logic are written into a Stored Procedure, they become part of a single execution plan on the server.

The results do not need to be returned to the client to have the conditional logic applied; all of the work is done on the server. This results in tremendous performance boosts when Stored Procedures are called repeatedly.

Efficient reuse of code and programming abstraction Stored Procedures can be used by multiple users and client programs. If you utilize them in a planned manner then you'll find the development cycle requires less time. Enhanced security controls You can grant users permission to execute a Stored Procedure independently of underlying table permissions. User Defined Functions Like functions in programming languages, SQL Server User Defined Functions are routines that accept parameters, perform an action such as a complex calculation, and returns the result of that action as a value.

The return value can either be a single scalar value or a result set. Functions in programming languages are subroutines used to encapsulate frequently performed logic. For more about functions, please refer to the article Different types of Functions. Even a procedure can return zero or n values. Functions can have only input parameters for it whereas Procedures can have input or output parameters. Functions can be called from Procedure whereas Procedures cannot be called from a Function.

Functions that return tables can be treated as another rowset. This can be used in JOINs with other tables. An exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function. We can use Transactions in Procedure whereas we can't use Transactions in Function.

Summary In this article, you learn the difference between Stored Procedure and Function. Connect and share knowledge within a single location that is structured and easy to search.

What does it mean if someone says that views create a virtual table, while procedures create a materials table? A view represents a virtual table. You can join multiple tables in a view and use the view to present the data as if the data were coming from a single table.

A stored procedure uses parameters to do a function Creating Views and Stored Procedures - has some information from Microsoft as to when and why to use each. Plenty of info available here. First you need to understand, that both are different things. You should use both of them. In views you cannot alter the data. A view references one or more existing database tables or other views. It is the snap shot of the database whereas a stored procedure is a group of Transact-SQL statements compiled into a single execution plan.

View is simple showcasing data stored in the database tables whereas a stored procedure is a group of statements that can be executed. A view is faster as it displays data from the tables referenced whereas a store procedure executes sql statements. Check this article : View vs Stored Procedures.

Exactly what you are looking for. A store procedure is used when simple SQL just isn't enough. Store procedures contain variables, loops and calls to other stored procedures. It's a programming language, not a query language. Views are static. Think of them as new tables with a certain layout and the data in them is created on the fly using the query you created it with.

The depends on the database. Simple views just run the query and filter the result. But databases like Oracle allow to create a "materialized" view which is basically a table which is updated automatically when the underlying data of the view changes.

A materialized view allows you to create indexes on the columns of the view especially on the computed columns which don't exist anywhere in the database.

Mahesh is not quite correct when he suggests that you can't alter the data in a view. So with patrick's view.