10. Stored Procedures

Stored Procedures in SQL

A stored procedure is a series of SQL statements compiled and saved to the database. By encapsulating complex logic in one place, they offer several advantages, such as faster execution, reduced network traffic, and the ability to include conditional logic (e.g., IF...ELSE statements) and parameters.

Key Points about Stored Procedures

• Stored Execution

  A stored procedure’s SQL statements are compiled once and can be run many times, reducing overhead and improving performance.

• Conditional Programming

  Procedures can include logical branches (IF, ELSE, etc.), enabling more advanced decision-making on the server side.

• Parameter Support

  You can pass parameters to procedures, allowing them to handle dynamic data during runtime.

• Network Efficiency

  Since the procedure is stored and processed on the server, only minimal data needs to travel between the client and server.

• Comparison with Functions
  • A function must always return exactly one value (scalar or table), whereas a procedure may or may not return a value.
  • You can call a function directly in a SELECT statement, but you cannot call a procedure in SELECT.
  • Procedures can perform certain DDL operations that functions cannot.
  • Procedures are often faster than functions due to compiled execution and the ability to bundle multiple operations.
  • Unlike functions, a procedure can output multiple values (not just one).
  • Error or status codes are returned by procedures, where 0 typically indicates success.

Procedure Example: Naming Convention

It is common to prefix stored procedure names with sp_ to indicate they are procedures, though this is more of a convention than a strict requirement.

Controlling Output Messages

SET NOCOUNT ON;

Prevents SQL Server from returning the “X row(s) affected” message after each statement.

@@ROWCOUNT

@@ROWCOUNT is a system variable that tracks how many rows were affected by the last executed statement in a batch.

Status Codes

Procedures typically return a status code to indicate success or an error:

• 0 indicates successful execution.

• Any non-zero value usually indicates an error code.

Executing Dynamic SQL inside Procedures

sp_sqlexecute (or sp_executesql in many SQL Server systems) can be used to run SQL statements that are built dynamically, often passed as NVARCHAR strings.

Stored Procedures vs. Functions

• Stored procedures cannot be called inside a function because they can contain more complex or transactional operations (including ALTER or other DDL statements).

• Functions are more restricted, focusing on returning a single value or a result set, and must be deterministic regarding side effects.

References

The content in this document is based on the original notes provided in Azerbaijani. For further details, you can refer to the original document using the following link:

Original Note - Azerbaijani Version