Procedimentos armazenados em SQL | Uma visão geral

Neste blog, vamos discutir procedimentos armazenados em SQL que você pode utilizar para salvar um conjunto de código e usá-lo repetidamente sempre que precisar. Os procedimentos armazenados são semelhantes às exibições. No entanto, você pode realizar operações de tabela comuns , como DROP, TRUNCATE, DELETE, etc. com procedimentos armazenados que não podem ser executados com visualizações.

Os procedimentos armazenados também são pré-compilados para que sejam executados mais rapidamente do que as exibições. Também minimiza a quantidade de dados enviados ao servidor de banco de dados.

Índice

• Sintaxe para procedimentos armazenados em SQL: sem parâmetros
• Sintaxe para procedimentos armazenados em SQL: com parâmetros
• Cenário de amostra de procedimentos armazenados no SSMS
• Segundo Cenário de Amostra
• Terceiro Cenário de Amostra
• Localização dos procedimentos armazenados salvos no SQL
• Criando um procedimento armazenado em SQL com parâmetros
• Criando um procedimento armazenado com um valor padrão
• Conclusão

Sintaxe para procedimentos armazenados em SQL: sem parâmetros

Para criar um procedimento armazenado sem parâmetros, você precisa começar com a função CREATE . Em seguida, adicione a função PROC , digite o nome do procedimento logo após e adicione a função AS .

A criação de um procedimento armazenado em é semelhante à criação de tabelas e exibições. No entanto, a diferença está no processo de obtenção de dados.

Por exemplo, se quisermos obter dados de uma visualização, usamos “ SELECT * FROM view_name .” Por outro lado, para procedimentos armazenados, usamos o EXEC que significa “executar” e depois digitamos o nome do procedimento armazenado .

Depois de executar um procedimento armazenado , ele também exibirá as informações com base nas instruções ou comandos que você adicionou.

Sintaxe para procedimentos armazenados em SQL: com parâmetros

Vamos . O processo é semelhante ao exemplo anterior que demonstrei. A única diferença é que você precisa incluir os parâmetros antes da função AS e também o tipo de dados logo após.

Você também pode utilizar o parâmetro que declarou na criação do procedimento armazenado em seus comandos. Isso é para você economizar tempo inserindo vários valores. Por exemplo, em vez de digitar manualmente customerID1 , customerID2 e assim por diante, utilizamos @custid ou o parâmetro para evitar um longo conjunto de códigos.

Observe que ao usar ou incluir um parâmetro, é importante usar o símbolo @ . Você também pode incluir quantos parâmetros quiser, não apenas um. É assim que executamos stored procedures com parâmetros.

Como você pode notar, também usamos o parâmetro com valor igual a 1 na execução do procedimento armazenado. Isso trará apenas as informações de custid1 .

Cenário de amostra de procedimentos armazenados no SSMS

Vamos ver mais exemplos de stored procedures em SQL. Na primeira instância, criarei um procedimento armazenado de amostra usando o comando destacado abaixo.

Para convenção de nomenclatura na criação de um procedimento armazenado, geralmente usamos “usp” ou “sp” para indicar que este é um procedimento armazenado. Se você está se perguntando o que significa “usp”, ele simplesmente significa procedimento armazenado definido pelo usuário. 

Em seguida, vamos escrever consultas para o procedimento armazenado processar quando for executado.

As consultas no exemplo anterior simplesmente removerão a tabela denominada dbo.stageOrders . Em seguida, ele recriará o dbo.stageOrders com os dados da tabela Sales.SalesOrderHeader . 

Vamos criar este procedimento armazenado destacando o código a seguir e clicando no botão Executar .

Depois disso, você deve ver uma mensagem como esta.

Em seguida, executaremos o procedimento armazenado executando o EXEC usp_TEST .

Após a execução do procedimento armazenado usp_TEST , o dbo.stageOrders agora deve ter os dados de Sales.SalesOrderHeader . Vamos verificar o conteúdo de dbo.stageOrders executando o comando abaixo.

Como resultado, esses são os dados que criamos em dbo.stageOrders com base nos comandos que adicionamos ao nosso procedimento armazenado.

Segundo Cenário de Amostra

Vamos para outro exemplo. Desta vez, não quero descartar a tabela dbo.stageOrders e recriar a mesma tabela com dados provenientes da tabela Sales.SalesOrderHeader .

Em vez disso, quero apenas que os dados da tabela Sales.SalesOrderHeader sejam exibidos sempre que eu executar o procedimento armazenado “ usp_TEST ” .

Para fazer isso, preciso fazer alterações no procedimento armazenado criado recentemente usando a instrução ALTER .

Neste exemplo, utilizamos a instrução ALTER que é usada para modificar nosso procedimento armazenado. Você também pode aplicar isso a exibições e tabelas. Depois de executar o código na captura de tela acima, ele deve alterar as consultas a serem processadas pelo nosso procedimento armazenado.

Desta vez, não recriará nenhuma tabela como fizemos anteriormente. Se executarmos “ usp_TEST ,” ele trará apenas os dados da tabela Sales.SalesOrderHeader .

Terceiro Cenário de Amostra

Vamos fazer outro exemplo. Desta vez, vamos reutilizar a tabela dbo.stageOrders que tínhamos em nosso primeiro exemplo de criação de um procedimento armazenado no SSMS . Começaremos com este exemplo selecionando a tabela dbo.stageOrders. 

Os resultados ao selecionar todos os dados de dbo.stageOrders devem ser assim.

Agora vamos esvaziar a tabela dbo.stageOrders executando usp_TEST . Podemos fazer isso adicionando o seguinte comando.

Depois de adicionar o comando TRUNCATE TABLE , precisamos atualizar usp_TEST .

Depois disso, uma mensagem dizendo que os comandos foram concluídos com sucesso deve ser exibida. Em seguida, executaremos usp_TEST novamente.

Ao executar usp_TEST , mostrará todos os registros de Sales.SalesOrderHeader e também esvaziará a tabela  dbo.stageOrders .

Para verificar se a tabela dbo.stageOrders está vazia, precisamos selecioná-la usando o seguinte comando e executar.

Ao executar o código acima, podemos ver que a tabela dbo.stageOrders agora está vazia. Isso se deve ao comando TRUNCATE TABLE que usamos para atualizar usp_TEST .

Localização dos procedimentos armazenados salvos no SQL

Se você quiser ver onde o procedimento armazenado em SQL está sendo salvo, vá para o painel Object Explorer no lado esquerdo e clique no ícone “ + ” antes do banco de dados que você está trabalhando.

Em seguida, clique com o botão direito do mouse em Programabilidade e selecione Atualizar .

Expanda a pasta ou grupo Programabilidade clicando no ícone “ + ”. Em seguida, expanda o grupo Procedimentos armazenados executando a mesma etapa. Dentro do grupo Stored Procedures, você deve ver o dbo.usp_TEST .

Se você quiser verificar quais comandos ou consultas um procedimento armazenado específico no SQL está executando, clique com o botão direito do mouse em um procedimento armazenado e siga as etapas na captura de tela abaixo.

Depois disso, ele abrirá o procedimento armazenado em uma guia diferente, onde você poderá ver os comandos fornecidos nele. É assim que o dbo.usp_TEST se parece ao abri-lo.

Como você pode ver, existem alguns comandos padrão antes da instrução CREATE . Você pode simplesmente excluí-lo se quiser. 

Agora você sabe como verificar quais comandos um procedimento armazenado em SQL está executando.

Criando um procedimento armazenado em SQL com parâmetros

Em seguida, vamos criar um procedimento armazenado com parâmetros. Por exemplo, vamos usar o código a seguir para criar um novo procedimento armazenado.

No código de exemplo, usei o mesmo processo de criação de um procedimento armazenado chamado usp_GetCustomer . Em seguida, adicionei um parâmetro que é @CustomerID com um tipo de entrada INT .

Observe que, depois de adicionar um parâmetro na criação de um procedimento armazenado, você sempre deve fornecer um parâmetro sempre que for executar um comando.

Vamos ver o que acontecerá se executarmos usp_GetCustomer sem fornecer um parâmetro.

Ao executar usp_GetCustomer sem parâmetro, apareceu uma mensagem de erro. É assim que ficaria se executássemos usp_GetCustomer com um parâmetro.

Com um parâmetro fornecido, podemos obter um resultado adequado na execução de nosso procedimento armazenado .

Criando um procedimento armazenado com um valor padrão

Se você deseja evitar receber uma mensagem de erro ao executar um procedimento armazenado com um parâmetro, pode definir um valor padrão que servirá como parâmetro padrão.

Por exemplo, vamos criar um procedimento armazenado chamado usp_GetOrdersByYear .

Em seguida, adicionarei um parâmetro @OrderYear com o tipo de entrada “ INT ” e um valor padrão igual a 2011 .

Se executarmos usp_GetOrdersByYear sem um parâmetro fornecido, ele exibirá registros com o ano 2011 .

Por outro lado, se executarmos usp_GetOrdersByYear com 2014 como parâmetro, ele deverá exibir registros com o ano 2014 .

É assim que você utiliza procedimentos armazenados em suas tarefas diárias de gerenciamento de dados.

Cláusula HAVING em funções de agregação SQL
ISNULL Função SQL em instruções de caso
Extração de dados SQL usando OFFSET e FETCH

Conclusão

Levando tudo em consideração, você aprendeu o que é um procedimento armazenado em SQL e sua finalidade. Também discutimos a instrução ALTER que é usada para fazer alterações ou atualizações em um procedimento armazenado atual.

Além disso, você aprendeu que existem métodos diferentes para criar procedimentos armazenados em SQL e como evitar erros ao executar um procedimento armazenado fornecendo um valor padrão.

Mais importante, você aprendeu a utilizar procedimentos armazenados para armazenar conjuntos de comandos para evitar a execução repetida de conjuntos longos de código. Como lembrete final, não se esqueça de usar o símbolo “ @ ” ao fornecer um parâmetro.

Tudo de bom,

Hafiz