SQL Server Procedures Cheat Sheet
Published: 2023-11-28 17:42:15


# Prerequisites
First of all, you need to know those concepts before diving into this article:
1. You gotta know what's a database system
2. You gotta know what's a database procedure
3. You gotta know that stored procedures may vary between database systems you choose

## What's a database system

A database system is a software solution that manages, organizes, and provides access to data. It is designed to efficiently store, retrieve, and manipulate large volumes of data while ensuring data integrity, security, and concurrency control. A database system typically includes a database management system (DBMS) and the data itself.

## What's a procedure

Database Procedures (sometimes referred to as Stored Procedures or Procs) are subroutines that can contain one or more SQL statements that perform a specific task. They can be used for data validation, access control, or to reduce network traffic between clients and the DBMS servers. Extensive and complex business logic can be embedded into the subroutine, thereby offering better performance.

## Differences between MySQL, PostgreSQL and SQL Server stored procedures 
They all support stored procedures as a way to encapsulate and execute a series of SQL statements, there are variations in syntax, features, and functionality. Here are some key points of difference:

1.  **Syntax Differences:**
    
    1.1   SQL Server uses Transact-SQL (T-SQL) for stored procedures.
    1.2 MySQL uses its own procedural language known as MySQL Stored Procedure Language.
    1.3 PostgreSQL uses PL/pgSQL as its procedural language.
    
2.  **Procedural Language Features:**
    
    2.1  SQL Server's T-SQL offers a rich set of procedural programming features, including control-of-flow constructs, error handling, and transaction management.
    2.2 MySQL's stored procedure language is similar to T-SQL but may have some syntactic differences and a slightly different set of features.
    2.3  PostgreSQL's PL/pgSQL is a versatile and powerful language, similar to PL/SQL in Oracle, and provides features like loops, conditional statements, and exception handling.
    
3.  **Transaction Management:**
    
    3.1   Transaction handling may have subtle differences. For example, the way transactions are started, committed, or rolled back can vary.
    3.2  SQL Server often uses `BEGIN TRANSACTION`, `COMMIT`, and `ROLLBACK` statements.
    3.3  MySQL and PostgreSQL have their own equivalents, but the syntax and behavior might differ.
 
4.  **Error Handling:**
    
    4.1  SQL Server provides `TRY...CATCH` blocks for structured error handling.
    4.2  MySQL uses `DECLARE ... HANDLER` for handling exceptions.
    4.3  PostgreSQL has `EXCEPTION` blocks for error handling.

5.  **Support for Advanced Features:**
    
     Each database system may have its own set of advanced features and extensions for stored procedures. For example, PostgreSQL supports various languages beyond PL/pgSQL, allowing you to use languages like PL/Python, PL/Perl, etc., for stored procedures.

6.  **Security Model:**
    
      Security models for stored procedures can differ. SQL Server, for instance, integrates with Windows authentication, whereas MySQL and PostgreSQL often rely on their user management systems.

7.  **Tooling and Management:**
    
     The tools available for developing, managing, and debugging stored procedures can vary between database systems. SQL Server has SQL Server Management Studio (SSMS), MySQL uses tools like MySQL Workbench, and PostgreSQL has tools like pgAdmin.

8.  **Compatibility:**
    
    Portability of stored procedures between these databases may be limited due to differences in syntax and features. If you plan to switch database systems, you might need to modify your stored procedures accordingly.
    
# What's the motivation behind using database procedures?
Database procedures are a good feature to encapsulate business logic, validate fields, and secure your data. Nevertheless, in my opinion, I would never use procedures to achieve those goals. The best way to do those things is to use an API, where you can encapsulate them.

I prefer to use procedures to retrive some informations about the database entities, like tables, users and indexes.

For exemple, you could be curious about some scheme details or a table's columns data types.

# Cheat Sheet

| Stored Procedure | Description |
|  --------  |  -------  |
|`sp_help`|Provides information about a database, a table, a view, or an index.|
|`sp_who`|Displays information about current users and processes.|
|`sp_configure`|Displays or changes global configuration settings.|
|`sp_who2`|Similar to sp_who but provides more detailed information.
|`sp_spaceused`|Displays the amount of space used and reserved by a table.
|`sp_rename`|Renames a user-created object in the current database.
|`sp_helptext`|Displays the definition of a user-defined rule, default, or trigger.
|`sp_adduser`|Adds a new SQL Server login account.
|`sp_addlogin`|Adds a new login for SQL Server.
|`sp_helpindex`|Displays information about indexes on a table.
|`sp_depends`|Displays information about object dependencies.
|`sp_helpconstraint`|Displays information about the constraints on a table.

# Usage
Into a SQL query, you can run this command to check a table information:

`exec sp_help 'table_name'`