Conceitos de procedimento armazenado

O procedimento armazenado é uma coleção de uma ou mais instruções T-SQL reunidas em um grupo de unidades de processamento lógico e armazenadas no servidor de banco de dados. Quando o procedimento armazenado é executado, na primeira chamada, o SQL Server irá executá-lo e armazená-lo em um cache, chamado cache do plano, nas próximas vezes o SQL Server reutilizará o cache do plano, para acelerar o processamento. Isso é muito eficaz na otimização de grandes bancos de dados com aplicativos de alto desempenho

Além disso, os procedimentos armazenados são extremamente convenientes para os administradores de banco de dados (DBAs), pois ajudam o DBA a criar blocos de comandos pré-nomeados e enviá-los aos desenvolvedores sem se preocupar com o conteúdo dos procedimentos armazenados. Apenas cuide dos parâmetros de entrada e saída. Por exemplo, você escreve um stored procedure que recupera a lista de pedidos do mês, e então entrega para o dev integrar na aplicação, o dev não precisa interferir no banco de dados para escrever novo código, basta chamar o stored procedimento que você declarou e apenas use-o.

Por exemplo, você escreve um procedimento armazenado para obter uma lista dos produtos mais vendidos por dia, então você envia isso armazenado para o departamento de desenvolvimento com o manual do usuário, então o departamento DEV não está interessado no conteúdo interno, mas apenas precisa de informações sobre os parâmetros passados e o resultado de retorno do armazenado.

Benefícios do uso de procedimentos armazenados:

• Melhore a segurança: os administradores de banco de dados podem aumentar a segurança associando privilégios a procedimentos de armazenamento. Os usuários podem obter permissão para executar procedimentos armazenados mesmo que não tenham acesso a tabelas ou exibições.
• Execução pré-compilada: O procedimento armazenado é compilado na primeira execução. Para cada execução subsequente, o SQL Server reutiliza a versão pré-compilada, o que reduz o tempo de execução e aumenta o desempenho.
• Tráfego cliente/servidor reduzido: o procedimento armazenado ajuda a reduzir o tráfego do sistema, quando a instrução T-SQL é executada, a rede utiliza recursos separados para cada execução. Quando o procedimento armazenado é executado, as instruções SQL são agrupadas e executadas como uma unidade, reduzindo o tráfego de rede.
• Reutilização de código: Os procedimentos armazenados podem ser reutilizados muitas vezes, o que elimina a necessidade de inserir repetidamente centenas de instruções Transact-SQL sempre que uma tarefa semelhante é executada.

Tipos de procedimentos armazenados

Procedimentos armazenados definidos pelo usuário :

podem ser chamados de procedimentos armazenados personalizados, esses procedimentos são usados para reutilizar instruções T-SQL para o processamento de tarefas lapwj. Existem dois tipos de procedimentos armazenados definidos pelo usuário: procedimentos armazenados T-SQL e procedimentos armazenados Common Language Runtime (CLR). Os procedimentos armazenados CLR são baseados em vários métodos do .NET Framework, que podem receber e retornar parâmetros.

Estenda os procedimentos armazenados:

Estender o procedimento armazenado ajuda o SQL Server a interagir com o sistema operacional. Os procedimentos armazenados estendidos não estão em objetos do SQL Server. Eles são procedimentos armazenados desenvolvidos como bibliotecas de vínculo dinâmico (DDL) que são executados fora do ambiente do SQL Server. O aplicativo interage com o SQL Server e chama a DLL durante a execução, o SQL Server aloca espaço para executar os procedimentos armazenados estendidos . Os procedimentos armazenados estendidos usam o prefixo 'xp'

Procedimentos armazenados do sistema:

Os procedimentos armazenados do sistema são comumente usados para interagir com as tabelas do sistema e lidar com tarefas administrativas, como atualização de tabelas do sistema, procedimentos armazenados do sistema com o prefixo 'sp_'. Esses procedimentos estão localizados no banco de dados Resource . Os procedimentos podem ser encontrados no esquema sys de cada sistema ou banco de dados definido pelo usuário. Os procedimentos armazenados do sistema permitem permissões GRANT, DENY, REVOKE .

Um procedimento armazenado do sistema é um conjunto pré-compilado de instruções T-SQL que serão executadas como uma unidade. Os procedimentos do sistema são usados na administração do banco de dados e no gerenciamento das operações e informações do sistema.Esses procedimentos fornecem uma solução para acessar facilmente informações de metadados sobre objetos do banco de dados, como tabelas do sistema, tabelas definidas pelo usuário, visualizações, índices.

Os procedimentos armazenados do sistema aparecem logicamente no esquema sys do sistema e no banco de dados definido pelo usuário. Ao fazer referência a procedimentos armazenados do sistema , o identificador de esquema sys é usado. Os procedimentos armazenados do sistema no sistema são armazenados fisicamente em um banco de dados oculto dentro de um recurso de banco de dados prefixado com sp_ . Os procedimentos armazenados do sistema são de propriedade do administrador do banco de dados (o nível mais alto de administração no sistema de banco de dados).

Nota: As tabelas do sistema são criadas por padrão no momento da criação de um novo banco de dados, essas tabelas armazenam informações de metadados sobre objetos definidos pelo usuário, como tabelas e visualizações. Os usuários não podem acessar ou atualizar tabelas do sistema usando procedimentos armazenados do sistema, a menos que tenham privilégios concedidos pelo administrador do banco de dados.

Classificação de Procedimentos Armazenados do Sistema

• Procedimentos Armazenados no Catálogo: Todas as informações sobre as tabelas do banco de dados são armazenadas em um conjunto de tabelas chamado catálogo do sistema. As informações do catálogo do sistema podem ser recuperadas usando os procedimentos do catálogo. Por exemplo, L sp_tables é um catálogo de procedimentos armazenados que exibe uma lista das tabelas do banco de dados atual
• Procedimentos armazenados de segurança: Os procedimentos armazenados de segurança são usados para gerenciar a segurança no banco de dados. Por exemplo, sp_changedbowner é um procedimento armazenado de segurança usado para alterar o proprietário do banco de dados atual.
• Procedimentos Armazenados de Cursor: Os procedimentos de cursor são usados para implementar recursos de ponteiro. Por exemplo sp_cursor_list é um procedimento armazenado que recupera todos os ponteiros abertos pela conexão e descreve suas propriedades.
• Procedimentos armazenados de consulta distribuída: Os procedimentos armazenados distribuídos são usados para administrar consultas distribuídas. Por exemplo, sp_indexes é um procedimento armazenado de consulta distribuída que retorna informações de índice para uma determinada tabela.
• Database Mail e SQL Mail Stored Procedures: usado para lidar com tarefas que trabalham com email no SQL Server. Por exemplo, sp_send_dbmail é um procedimento armazenado de email de banco de dados que envia um email para um destinatário especificado. O conteúdo do email pode ser um conjunto de resultados, arquivo de anexo ou ambos.

Procedimentos Armazenados Temporários

Os procedimentos armazenados criados para uso temporário em uma sessão são chamados de procedimentos armazenados temporários . Esses procedimentos são armazenados na tabela tempdb . A tabela de sistema tempdb é um recurso global disponível para todos os usuários que se conectam por meio da instância do SQL Server. Ele contém todas as tabelas temporárias e procedimentos armazenados temporários.

O SQL Server oferece suporte a dois tipos de procedimentos armazenados temporários, locais e globais, com as seguintes diferenças:

Nota: Uma sessão é estabelecida quando o usuário se conecta ao banco de dados e termina quando o usuário se desconecta. O nome completo do procedimento armazenado temporário global, incluindo o prefixo ##, não pode exceder 128 caracteres. O nome final do procedimento armazenado temporário local, incluindo o prefixo #, não pode exceder 116 caracteres.

Procedimentos de armazenamento remoto

Os procedimentos armazenados que podem ser executados no SQL Server remoto são chamados de procedimentos armazenados remotos. O procedimento soted remoto pode ser usado somente se o servidor permitir acesso remoto (acesso remoto, não acesso local). Quando o procedimento armazenado remoto é executado de uma instância local do SQL Server para o cliente, a instrução pode encontrar um erro de anulação. Quando um erro for encontrado, o comando que causou o erro será encerrado, mas o procedimento armazenado remoto continuará a ser executado.

Procedimentos armazenados estendidos

Os procedimentos armazenados estendidos são usados para lidar com tarefas que não podem ser tratadas por sintaxes T-SQL normais. Os procedimentos armazenados estendidos usam o prefixo 'xp_' . Esses procedimentos armazenados serão armazenados no esquema dbo do banco de dados mestre.

Sintaxe:

 EXECUTE <procedure_name>

Exemplo usando o procedimento armazenado estendido xp_fileexist para verificar se o arquivo Mytext.txt existe:

 EXECUTE xp_fileexist 'CMyTest.txt'

Procedimento Armazenado Definido pelo Usuário

No SQL Server, os usuários têm permissão para criar procedimentos armazenados personalizados para executar diferentes tarefas, esses procedimentos armazenados são chamados de procedimentos armazenados personalizados ou definidos pelo usuário.

Por exemplo, se a tabela Customer_Details armazena todos os dados do cliente, precisaríamos escrever instruções T-SQL sempre que quisermos visualizar os dados detalhados sobre o cliente. Em vez disso, podemos criar um procedimento armazenado personalizado que exibe detalhes do cliente cada vez que o procedimento é executado para reutilização.

A criação de um procedimento armazenado personalizado requer a permissão CREATE PROCEDURE no banco de dados e a permissão de esquema ALTER para o procedimento criado.

Sintaxe:

 CREATE {PROC|PROCEDURE} proc_ame [{@parameter data_type}] AS <sql_statement>

Exemplo de inicialização de um procedimento armazenado personalizado chamado uspGetCustTerritory usado para exibir detalhes do cliente:

 use AdventureWorks2019 CREATE PROCEDURE uspGetCustTerritory AS SELECT TOP 10 CustomerID,Customer.TerritoryID,Sales.SalesTerritory.Name FROM Sales.Customer JOIN Sales.SalesTerritory ON Sales.Customer.TerritoryID = Sales.SalesTerritory.TerritoryID go exec uspGetCustTerritory

Usando parâmetros (parâmetros)

O poder do procedimento armazenado vem de poder colocar parâmetros dentro dele, os dados serão passados para o procedimento armazenado através do programa chamador, existem 2 tipos de parâmetros da seguinte forma:

• Os parâmetros de entrada permitem que o programa de chamada passe valores para o procedimento armazenado. Esses valores são capturados na variável definida nos procedimentos armazenados.
• Os parâmetros de saída permitem que o procedimento armazenado passe valores de volta para o programa de chamada. Esses valores são captados na variável do programa chamador.

Parâmetros de entrada

Os valores são passados do programa chamador para o procedimento armazenado, esses valores são capturados na variável definida nos procedimentos armazenados. O parâmetro de entrada é definido no momento da criação do procedimento armazenado. Os valores passados para o parâmetro de entrada podem ser variáveis ou constantes, o valor será passado para o procedimento armazenado no momento da chamada do procedimento. Os procedimentos de armazenamento tratam de tarefas específicas usando esses valores.

Sintaxe:

 CREATE PROCEDURE <procedure_name> @parameter <data_type> AS <sql_statement> EXEC| EXECUTE <procedure_name> <parameters>

Por exemplo, crie um procedimento armazenado uspGetSales com o parâmetro território para obter o nome do território e exibir os detalhes da venda e a ID do vendedor desse território. Em seguida, o código que executa o procedimento armazenado com o valor 'Northwest' será passado como parâmetro de entrada:

 use AdventureWorks2019; GO CREATE PROCEDURE uspGetSales @territory varchar(40) AS SELECT BusinessEntityID, B.SalesYTD, B.SalesLastYear FROM Sales.SalesPerson A JOIN sales.SalesTerritory B ON A.TerritoryID = B.TerritoryID WHERE B.Name=@territory; GO exec uspGetSales 'Northwest'

Parâmetros de saída

Os procedimentos armazenados às vezes precisam retornar a saída para o programa de chamada. Ele passará dados do procedimento armazenado para o programa de chamada e será processado por meio de parâmetros de saída . Os parâmetros de saída são definidos no tempo do procedimento armazenado. Para especificar o parâmetro de saída, a palavra-chave OUTPUT é usada ao declarar o parâmetro. E chamar a instrução também precisa definir a variável com a palavra-chave OUTPUT.

Sintaxe:

 CREATE PROCEDURE <procedure_name> @parameter <data_type> OUTPUT AS <sql_statement> EXEC| EXECUTE <procedure_name> <parameters>

Exemplo de procedimento armazenado upsGetTotalSales com parâmetro de entrada @territory para obter o nome do território e parâmetro de saída @sum para obter o total de vendas do ano até o momento:

 use AdventureWorks2019; GO CREATE PROCEDURE uspGetTotalSales @territory varchar(40), @sum int OUTPUT AS SELECT @sum= SUM(B.SalesYTD) FROM Sales.SalesPerson A JOIN Sales.SalesTerritory B ON A.TerritoryID = B.TerritoryID WHERE B.Name = @territory GO

Na próxima etapa, executamos o procedimento armazenado acima declarando a variável @sumsale para receber o valor de saída.

 DECLARE @sumsale int; exec uspGetTotalSales 'NorthWest', @sumsale OUTPUT; SELECT @sumsale AS 'Total sales northWest';

Os parâmetros OUTPUT têm as seguintes propriedades:

• Não é possível transportar tipo de dados de texto ou imagem
• A instrução de chamada deve conter uma variável para receber o valor de saída.
• Variável que pode ser usada no próximo retorno de chamada da instrução T-SQL para retornar ao usuário

A cláusula OUTPUT retorna informações para cada linha em que as instruções INSERT, UPDATE e DELETE foram executadas. Esta cláusula é útil para obter identidade, ou valores de coluna computados após realizar uma operação INSERT ou UPDATE.

Claro, também podemos usar o SSMS para criar Stored Procedures .

ALTER (Modificar) Procedimento Armazenado

As permissões associadas ao procedimento armazenado são perdidas quando o procedimento armazenado é reinicializado. No entanto, quando modificadas por meio da instrução ALTER, as permissões definidas para o procedimento permanecem no estado.

Sintaxe:

 ALTER PROCEDURE <procedure_name> @parameter <data_type> [OUTPUT] [WITH {ENCRYPTION|RECOMPILE}] AS <sql_statement>

Modifique a definição de um procedimento armazenado chamado uspGetTotals para adicionar uma coluna CostYTD na tabela Sales.SalesTerritory

 ALTER PROCEDURE [dbo].[uspGetTotal] @territory varchar = 40 AS SELECT BusinessEntityID, B.SalesYTD, B.CostYTD, B.SalesLastYear FROM Sales.SalesPerson A JOIN Sales.SalesTerritory B ON A.TerritoryID = B.TerritoryID WHERE B.Name = @territory GO

Observação: quando você altera a definição no procedimento armazenado, os objetos dependentes podem ter um erro de execução, esse problema ocorre se os objetos dependentes não forem atualizados para refletir a alteração do procedimento armazenado.

Procedimento armazenado DROP

Os procedimentos armazenados podem ser descartados quando não forem mais necessários. Se o programa chamar um procedimento excluído, um erro será retornado.

Se um novo procedimento for criado com o mesmo nome e os mesmos parâmetros do procedimento descartado, todas as chamadas para o procedimento antigo serão executadas com êxito, pois ele fará referência ao novo procedimento com o mesmo nome e os mesmos parâmetros do procedimento excluído.

Antes de descartar o procedimento armazenado, você pode executar um procedimento armazenado do sistema denominado sp_depends para identificar quais objetos dependem do procedimento.

Sintaxe:

 DROP PROCEDURE <procedure_name>

 DROP PROCEDURE uspGetTotals

Procedimentos armazenados aninhados

O SQL Server 2019 permite que procedimentos armazenados sejam chamados dentro de outros procedimentos armazenados. Essa arquitetura de procedimento armazenado chama outro procedimento armazenado chamado procedimento armazenado aninhado.

Quando um procedimento armazenado chama outro procedimento armazenado, diz-se que a camada de aninhamento é aumentada em um. Da mesma forma, quando o procedimento armazenado conclui a execução e passa o fluxo de controle para o procedimento armazenado de chamada, a camada de aninhamento é reduzida em um. A camada de aninhamento máxima suportada pelo SQL Server 2019 é 32.

Por exemplo:

 CREATE PROCEDURE NestedProcedure AS BEGIN EXEC uspGetCustTerritory EXEC uspGetSales 'France' END