DEV Community: JOAO GABRIEL SANTOS NEVES

[PT-BR] SQL Server Procedures

JOAO GABRIEL SANTOS NEVES — Tue, 28 Nov 2023 18:04:14 +0000

Pré-requisitos

Antes de mergulhar neste artigo, é necessário conhecer os seguintes conceitos:

Você precisa saber o que é um sistema de banco de dados.
Você precisa saber o que é uma procedure de banco de dados.
Você precisa estar ciente de que as procedures armazenadas podem variar entre os sistemas de banco de dados que você escolher.

O que é um sistema de banco de dados?

Um sistema de banco de dados é uma solução de software que gerencia, organiza e fornece acesso a dados. Ele é projetado para armazenar, recuperar e manipular eficientemente grandes volumes de dados, garantindo integridade, segurança e controle de concorrência dos dados. Um sistema de banco de dados normalmente inclui um sistema de gerenciamento de banco de dados (SGBD) e os próprios dados.

O que é uma procedure?

Procedures (às vezes chamados de Procedimentos Armazenados ou Procs) são sub-rotinas que podem conter uma ou mais instruções SQL que realizam uma tarefa específica. Elas podem ser usadas para validação de dados, controle de acesso ou para reduzir o tráfego de rede entre clientes e servidores de SGBD. Lógica de negócios extensa e complexa pode ser incorporada na sub-rotina, oferecendo assim melhor desempenho.

Diferenças entre stored procedures no MySQL, PostgreSQL e SQL Server

Embora todos eles suportem procedimentos armazenados como uma maneira de encapsular e executar uma série de instruções SQL, há variações na sintaxe, recursos e funcionalidades. Aqui estão alguns pontos-chave de diferença:

Diferenças de Sintaxe:

1.1 SQL Server usa Transact-SQL (T-SQL) para stored procedures.
1.2 O MySQL usa sua própria linguagem procedural conhecida como MySQL Stored Procedure Language.
1.3 O PostgreSQL usa PL/pgSQL como sua linguagem procedural.
Recursos de Linguagem Procedural:

2.1 O T-SQL do SQL Server oferece um conjunto rico de recursos de programação procedural, incluindo construções de controle de fluxo, tratamento de erros e gerenciamento de transações.
2.2 A linguagem de procedimento armazenado do MySQL é semelhante ao T-SQL, mas pode ter algumas diferenças sintáticas e um conjunto ligeiramente diferente de recursos.
2.3 O PL/pgSQL do PostgreSQL é uma linguagem versátil e poderosa, semelhante ao PL/SQL no Oracle, e fornece recursos como loops, declarações condicionais e tratamento de exceções.
Transaction Management:

3.1 O tratamento de transações pode ter diferenças sutis. Por exemplo, a maneira como as transações são iniciadas, confirmadas ou revertidas pode variar.
3.2 O SQL Server frequentemente usa instruções BEGIN TRANSACTION, COMMIT e ROLLBACK.
3.3 MySQL e PostgreSQL têm seus equivalentes, mas a sintaxe e o comportamento podem diferir.

Error Handling:

4.1 O SQL Server fornece blocos TRY...CATCH para tratamento estruturado de erros.
4.2 O MySQL usa DECLARE ... HANDLER para manipulação de exceções.
4.3 O PostgreSQL possui blocos EXCEPTION para tratamento de erros.

Suporte a Recursos Avançados:

Cada sistema de banco de dados pode ter seu próprio conjunto de recursos avançados e extensões para procedimentos armazenados. Por exemplo, o PostgreSQL suporta várias linguagens além do PL/pgSQL, permitindo o uso de linguagens como PL/Python, PL/Perl, etc., para procedimentos armazenados.

Modelo de Segurança:

Os modelos de segurança para procedimentos armazenados podem ser diferentes. O SQL Server, por exemplo, integra-se à autenticação do Windows, enquanto o MySQL e o PostgreSQL geralmente dependem de seus sistemas de gerenciamento de usuários.

Ferramentas e Gerenciamento:

As ferramentas disponíveis para desenvolver, gerenciar e depurar procedimentos armazenados podem variar entre os sistemas de banco de dados. O SQL Server possui o SQL Server Management Studio (SSMS), o MySQL usa ferramentas como o MySQL Workbench, e o PostgreSQL possui ferramentas como o pgAdmin.

Compatibilidade:

A portabilidade de procedimentos armazenados entre esses bancos de dados pode ser limitada devido a diferenças na sintaxe e nos recursos. Se você planeja mudar de sistema de banco de dados, pode ser necessário modificar seus procedimentos armazenados de acordo.

Qual é a motivação por trás do uso de procedimentos de banco de dados?

Procedures de banco de dados são uma boa feature para encapsular lógica de negócios, validar campos e proteger seus dados. No entanto, na minha opinião, eu nunca os usaria para alcançar esses objetivos. A melhor maneira de fazer essas coisas é usar uma API, onde você pode encapsulá-las.

Prefiro usar procedimentos para obter informações sobre as entidades do banco de dados, como tabelas, usuários e índices.

Tabela

Stored Procedure	Descrição
`sp_help`	Fornece informações sobre um banco de dados, uma tabela, uma visualização ou um índice.
`sp_who`	Exibe informações sobre usuários e processos atuais.
`sp_configure`	Exibe ou altera configurações globais.
`sp_who2`	Semelhante a sp_who, mas fornece informações mais detalhadas.
`sp_spaceused`	Exibe a quantidade de espaço usado e reservado por uma tabela.
`sp_rename`	Renomeia um objeto criado pelo usuário no banco de dados atual.
`sp_helptext`	Exibe a definição de uma regra, padrão ou trigger definido pelo usuário.
`sp_adduser`	Adiciona uma nova conta de login do SQL Server.
`sp_addlogin`	Adiciona um novo login para o SQL Server.
`sp_helpindex`	Exibe informações sobre índices em uma tabela.
`sp_depends`	Exibe informações sobre dependências de um objeto.
`sp_helpconstraint`	Exibe informações sobre as constraints de uma table.

Usabilidade

Em uma query SQL, você pode rodar o comando abaixo e recuperar a information de uma tabela, por exemplo:

exec sp_help 'table_name'

SQL Server Procedures Cheat Sheet

JOAO GABRIEL SANTOS NEVES — Tue, 28 Nov 2023 17:42:15 +0000

Prerequisites

First of all, you need to know those concepts before diving into this article:

You gotta know what's a database system
You gotta know what's a database procedure
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:

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.
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.
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.
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.
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.
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.
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.
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'