Overview of Stored Procedures¶
Stored procedures are loosely similar to functions; they help make code easier to maintain and re-use.
A stored procedure returns a single value. Although you can run
SELECT statements inside a stored procedure,
the results must be used within the stored procedure, or be narrowed to a single value to be returned.
In this Topic:
Benefits of Stored Procedures¶
The benefits of stored procedures include:
Stored procedures allow procedural logic (branching and looping) and error handling, which straight SQL does not support.
Stored procedures enable you to dynamically create a SQL statement and execute it.
Stored procedures allow you to write code that executes with the privileges of the role that owns the procedure, rather than with the privileges of the role that runs the procedure. This allows you to delegate the power to perform specified operations to users who otherwise could not do so. However, there are some limitations on these owner’s rights stored procedures.
One common use for stored procedures is to help automate a task that requires multiple SQL statements and is performed frequently. For example, suppose that you want to clean up a database by deleting data older than a specified date. You can write multiple DELETE statements, each of which deletes data from one specific table. You can put all of those statements in a single stored procedure and pass a parameter that specifies the cut-off date. Then you can clean up the database simply by calling the stored procedure. As your database changes, you can update the procedure to clean up additional tables; if there are multiple users who use the cleanup command, they can call the procedure, rather than remember every table name and clean up each table individually.
Differences Between Stored Procedures and UDFs¶
Both stored procedures and UDFs (user-defined functions) make it easier to write modular code. However, there are important differences between UDFs and stored procedures.
Stored Procedures Are Called as Independent Statements¶
A stored procedure is called as an independent statement, rather than as part of a statement. The code below illustrates the difference between calling a stored procedure and calling a function:
CALL MyStoredProcedure1(argument_1); -- Stored Procedure call SELECT MyFunction1(argument_1) FROM table1; -- Function call
Returning a Value from a Stored Procedure is Optional¶
A stored procedure is allowed to return a value, but is not required to return a value.
A function, on the other hand, is required to return a value.
Values Returned by Stored Procedures Are Not Directly Usable in SQL¶
Although a stored procedure can return a value, the syntax of the CALL command does not provide a place to store the returned value or a way to operate on it or pass it to another operation. There is no way in SQL to construct a statement like:
y = stored_procedure1(x); -- Not allowed
However, there are indirect ways to use the return value of a stored procedure:
You can call the stored procedure and then call the RESULT_SCAN function and pass it the statement ID generated for the stored procedure.
If the stored procedure is a caller’s rights stored procedure, you can store a result set in a temporary table, and use the temporary table after returning from the stored procedure call.
Of course, you can also store results in a permanent table and use those results after returning from the stored procedure.
If the volume of data is not too large, you can store multiple rows and multiple columns in a
VARIANT(for example, as a JSON value) and return that
Single Stored Procedure per CALL Statement¶
You can call only one stored procedure per CALL statement.
You cannot use a stored procedure call as part of an expression.
However, inside a stored procedure, the stored procedure can call another stored procedure, or call itself recursively. An example of this is shown in the code examples section Examples.
For more details about calling a stored procedure, see Calling a Stored Procedure.
Stored Procedures Can Access the Database and Issue Nested Queries via an API¶
The example below shows how a stored procedure can create and execute a SQL statement that calls another stored procedure:
Choosing to Create a Stored Procedure or a UDF¶
In general, when deciding whether to create a stored procedure or UDF:
- Create a stored procedure
If you are migrating an existing stored procedure from another application/system.
If you need to perform database operations:
Typical queries and DML, such as SELECT, UPDATE, etc.
Administrative tasks, including DDL such as deleting temporary tables, deleting data older than
Ndays, or adding users.
- Create a UDF
If you are migrating an existing UDF from another application/system.
If you need a function that can be called as part of a SQL statement and that must return a value that will be used in the statement.
If your output needs to include a value for every input row or every group. For example:
select MyFunction(col1) from table1; select MyAggregateFunction(col1) from table1 group by col2;