DEV Community: Allan Rodrigues

Unit Test em Stored Procedures (SQL Server) Parte II

Allan Rodrigues — Tue, 09 Jun 2020 18:22:44 +0000

E ai pessoal, como estamos?
Dando continuidade a nossa sequencia de posts sobre Testes Unitários em Stored Procedures, dessa vez vamos ver como avaliar se o valor retornado da nossa sp está de acordo com o valor esperado.

Vamos continuar usando nesse post:

Visual Studio Data Tools 2017
SQL Server 2016

Ainda utilizaremos a sp criada no post anterior, então se você perdeu a Parte 1 pode dar uma conferida aqui nesse LINK, beleza?

Bom, essa parte é bem mais curta que a primeira visto que já fizemos todas as configurações básicas como:

Criar a sp;
Importar a sp para o nosso projeto;
Configurar a conexão com a base de dados;
Realizar o primeiro teste unitário

Então vamos lá..
Para atingir o nosso objetivo que é verificar se o valor retornado da sp está correto, vamos criar uma nova unidade de teste. Então podemos clicar com o botão direito em cima da nossa sp e em "Create Unit Tests..."

Será exibida a tela acima, podemos configurar da seguinte forma:
Em project: Vamos deixar o nome do projeto que já havíamos criado.
Create New Class: vamos criar uma classe nova com o nome de UnitTestOperacao.cs (esse nome fica por sua conta).
E em seguida podemos finalizar com o OK.

Podemos observar agora em nosso Explorador de Soluções (Solutions Explorer) que uma nova unidade de teste foi criada:

Vamos ajustar agora o script de test que é gerado.
Certifique-se que está alterando o script correto, observando o nome do arquivo que está aberto.
Feito isso, faça as alterações devidas no script para que fique da forma como está abaixo:

De acordo com os inputs alterados queremos que seja realizado uma SOMA entre 2 + 2 e que o resultado seja 4.

Agora vamos alterar nossas condições de teste.
Na parte inferior remova o teste inconclusivo. E adicione a condição SCALAR VALUE, após adicionar essa condição aperte F4 para acessar as propriedades e em EXPECTED VALUE vamos colocar o valor 4. Com isso, o nosso teste espera que a SOMA entre 2+2 seja 4.

Adicionando a condição SCALAR VALUE

Ajustando a propriedade EXPECTED VALUE

Ao concluir as alterações, salve a solução e observe na janela "Gerenciador de Testes" que a nossa nova unidade está disponível.

Finalmente podemos colocar o nosso teste pra rodar.

Clique com o botão direito em cima da unidade criada e em "Executar Testes Selecionados"

Voilà! Nosso teste executado com sucesso... afinal de contas 2+2 ainda é 4.

Agora vamos mudar o valor de qualquer input para que o valor da soma seja diferente de 4.

Alterando o script como acima o resultado da soma será 3, o que é diferente de 4. Então nosso teste deverá apresentar erro.

E como esperado o teste retornou um erro, alertando que os valores são diferentes, o resultado 3 não coincide com o valor 4 esperado.

E assim concluímos a segunda parte do nosso post de Unit Test em Stored Procedures.
Sugiro, que vocês brinquem com os valores e operações para visualizar o comportamento do teste.

Até a próxima, onde falaremos sobre testes negativos e encerraremos essa sequência de posts.

Unit Test em Stored Procedures (Sql Server) Parte I

Allan Rodrigues — Tue, 09 Jun 2020 03:07:20 +0000

Atualmente temos acompanhado o mercado e visto muitas vagas de emprego que pedem ao desenvolvedor habilidades em construir testes unitários e conhecimento de ferramentas para aplicar testes front-end.
Porém pouco, ou nem isso, lembramos de criar rotinas para testes em nossas procedures, não é mesmo? Apenas algumas execuções simples e uma conferida rápida no resultado.
Se identificou, né?

Então devido essa realidade que tenho visto nos meus dias de trabalho resolvi começar a aplicar em meu dia-a-dia(sim, eu também não fazia) e compartilhar para quem se interessar em uma forma de realizar testes em suas procedures criadas no SQL Server. E ai meu velho? Vamo lá?

Nesse exemplo eu usei:

Visual Studio Data Tools 2017
SQL Server 2016
Banco de Dados de Estudo: WorldWideImporters

MÃOS NA MASSA!!!!

Primeiro vamos criar nossa procedure que será o objeto de teste. Uma procedure simples que realiza operações matemáticas e retorna um resultado.
Obs: Salve o script da procedure, pois será necessário mais a frente.

Depois da compilação da procedure vamos para o VS Data Tool 2017.
Com o VS aberto criamos um novo projeto, e em templates ou instalados buscamos por SQL Server Database Project.
Em seguida damos um nome ao projeto/solução e OK.

Após criado a solução, vamos em Projeto > Importar > Script (*.sql).
Lembra da procedure que foi criada lá no início? Pois é vamos importar agora para o nosso projeto. Seguindo a sequência de imagens a seguir.

1 - Selecione o arquivo do script .sql que foi salvo em alguma pasta do seu computador:

2 - Deixe nas opções padrões mesmo (eu não altero):

3 - O Visual Studio mostrará o progresso de importação.

4 - E.... Voilà! O seu objeto foi importado e aparecerá no Object Explorer abaixo:

Feito isso, clique com o botão direito em cima do objeto importado e então em "Criar Teste Unitário... (Create Unit Test...)"

Em seguida abrirá uma janela modal para configurar a conexão com o banco de dados, informe o seu servidor, usuário e senha de forma padrão na aba browse e clique em OK.

Assim concluímos mais um passo!!! UFA! Muita coisa, ne? Porém ainda temos um caminho pela frente.. siga firme!

Retomando....
Após criarmos a nossa conexão o Visual Studio nos mostrará uma tela com um script de chamada da procedure de teste, e no combobox superior esquerdo o objeto que está será testado e no combobox da direita nos dá três opções que são executadas na sequência:

Pre-test:(OPCIONAL) Nessa área podemos criar scripts de preparação de ambiente, como por exemplo create, inserts de dados necessários para o funcionamento da rotina.

Test:(OBRIGATÓRIO) Aqui será feito o script de teste mesmo.

Pos-Test:(OPCIONAL) Pode ser usado para limpar os dados utilizados durante o teste.

Nessa mesma tela vamos remover o teste inconclusivo que aparece na parte inferior..

E então vamos criar uma validação de teste bem simples! Apenas pra verificar se a procedure está retornando dados da operação informada.
Pra isso na mesma área onde removemos o teste inconclusivo, vamos incluir uma condição de teste.
Escolha no combobox a opção "Row Count" e então adicione.

Na sequencia, clique em cima da linha e pressione F4 para acessar as propriedades do teste e altere o valor de "ROW COUNT" de 0 para 1 e salve tudo.

Quando salvar tudo da solução, na aba de "Gerenciamento de Teste" irá aparecer o teste nós criamos, como na imagem abaixo:

E então vamos rodar o nosso teste!!!!

PRONTINHO!!
TESTE EXECUTADO COM SUCESSO.

Alteramos os parâmetros para valores válidos e executamos a rotina, parece meio demorado não é? Mas não é não e vale muito a pena, principalmente se você tem que documentar os resultados do teste.
Neste primeiro post de 3, fizemos o caso de sucesso.
No próximo vamos verificar o valor esperado e o último o teste negativo.

Até a próxima meus amigos.

Simulando a cláusula MERGE em uma Stored Procedure Compilada Nativamente.

Allan Rodrigues — Sun, 07 Jun 2020 04:33:42 +0000

O que é uma Stored Procedure Compilada Nativamente?

Em resumo SP's compiladas nativamente são procedures compiladas para código de máquina gerando uma DLL e carregando a mesma para o In-Memory OLTP Runtime e é usada para acessar tabelas otimizadas em memória.
Mais detalhes sobre esse tipo de SP você pode encontrar AQUI .

Mas esse tipo de procedure possui limitações e uma delas é a utilização do comando MERGE.

O que é o T-SQL MERGE?

Com o comando MERGE é possível realizar INSERT, UPDATE, DELETE na mesma instrução de acordo com a validação dos dados entre as tabelas origem e destino.
Abaixo a utilização do MERGE:

Mas como usar uma instrução tão poderosa em procedures compiladas nativamente? Segue abaixo um script equivalente, um workaround para isso.

Criamos uma tabela e um tipo
Script de criação da procedure que fará o Merge

Depois é só declarar uma váriavel do tipo que foi criado, inserir alguns valores e passar para procedure como parametro para testar.

Prontinho!

Temos uma procedure compilada nativamente que simula a execução do comando merge :D

Gerando um totalizador acumulado por linha sem usar cursor.

Allan Rodrigues — Fri, 05 Jun 2020 21:42:02 +0000

Olá pessoas!!!
Acredito que assim como eu vocês já se depararam com situações nas quais precisavam gerar um total que vai se acumulando linha a linha, não é mesmo?
E quando geramos consultas pra relatórios acabamos usando o famoso cursor para gerar esses totalizadores, o que acaba não sendo tão performático assim.
Dessa forma resolvi mostrar aqui como podemos gerar esse tipo de totalizador usando Window Function, no T-SQL.

Pra montar esse exemplo eu usei:

SQL SERVER 2016 Developer
SSMS
Banco de dados de estudo WorldWideImporters

Antes de metermos a mão na massa, vocês tão ligados do que são as Window Function?
Window Function são funções nas quais podemos utilizar para realizar cálculos ou agregações em um conjunto de linhas que possuem alguma relação, nesse caso não precisamos utilizar o GROUP BY. E torna muito útil pois na mesma consulta podemos ter um resultado analítico e sintético. A sua utilização é bem simples.

Nesse Link da documentação da Microsoft tem um detalhamento melhor sobre Window Function. Pois não é o caso aqui entrar em muito detalhe apenas vamos abordar um caso específico, lá você pode abrir a sua mente e viajar em possíveis soluções...

Hora de botar a mão na massa!!!!

No cenário em questão vamos imaginar que foi feito uma solicitação para você gerar uma consulta que mostre o ID do pedido, ID do Cliente, Data do Pedido, Descrição do pedido, Quantidade, Valor Unitário, Total Geral do pedido e um Total Acumulativo por pedido.
Abaixo nos tempos os Script para atender essa solicitação.

Na linha na qual geramos o total geral:
SUM(OL.UnitPrice * Quantity) OVER(PARTITION BY OL.OrderId) AS Total

Usamos a Window Function OVER() gerando uma partição com o PARTITION BY e com isso podemos somar os registros referente a um determinado ID sem precisar usar o GROUP BY.

Na linha na qual geramos o total corrente:
SUM(OL.UnitPrice * Quantity) OVER(PARTITION BY OL.OrderID ORDER BY OL.OrderID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS Total_Corrente

De forma semelhante ao total geral também criamos uma partição com o PARTITION BY para ele gerar o total corrente por ID do pedido, porém acrescentamos um ORDER BY que é obrigatório quando utilizamos UNBOUNDED PRECEDING.

UNBOUNDED PRECEDING indica que a Window Function irá começar da primeira linha da partição.

CURRENTE ROW indica que é até a linha corrente.

Então ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, seria as lihas entre o início da partição e a linha corrente. Com isso podemos ir gerando o total por linha da partição.

Abaixo o resultado da nossa consulta.

Então é isso pessoal, espero que isso seja útil pra vocês. :D

French Amortization Method (PRICE) with T-SQL.

Allan Rodrigues — Fri, 29 May 2020 22:42:28 +0000

What is FRENCH AMORTIZATION METHOD? (Also known as PRICE)

The amortization by the French method, progressive or classic, is characterized by the payment of overdue interest, that is to say, at the end of each payment, and payment of constant instalments in each period.
Interest decreases as loan periods pass and amortized capital increases in evey new period.
The follow image contains the formula to calculate a banking loan.

Mês = Month;
Prestação = Payment;
Juros = Interest;
Amortização = Amount amortized;
SD (Saldo Devedor) = Fixed Amount.

Translate to Transact SQL

You can download the script SQL_CALCULAR_PRICE.sql on my Github, to analyze how to calculate PRICE using T-SQL. Follow the link: