SQL Server Procedures Cheat Sheet

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'