Stored Procedures e Estruturas de Controle em SQL Server

exec spAlugaFita 110, 1, 10, 1
select * from resumo

Resolução do exercício: Stored Procedure

alter procedure spAlugaFita (@codLoc int, @codFita int, @valor decimal, @codCli int) as
begin
declare @existeFita int
declare @existeCli int

-- Verifica se a fita existe
set @existeFita = (select COUNT(*) from midias where midias.codMidia = @codFita)
set @existeCli = (select COUNT(*) from cliente where codCli = @codCli)

-- Pode ser substituído por:
-- if not exists (select codMidia from midias where midias.codMidia = @codFita) or
-- not exists (select codCli from cliente where codCli = @codCli)

if @existeFita = 0 or @existeCli = 0
print 'Fita ou Cliente inexistente!!!'
else
begin
-- Verifica se a locação já existe
if exists (select CodLocacao from Locacao where CodLocacao = @codLoc)
print 'Locação já registrada!!!!'
else
begin

-- Insere a locação
insert into locacao (codLocacao, dataLoc, valorTotalLoc, codCli) values
(@codLoc, GETDATE(), @valor, @codCli)

-- Insere nos itens
insert into itensLocacao (codLocacao, codMidia, dataDev) values
(@codLoc, @codFita, DATEADD(dd,3, GETDATE()))

set @existeCli = (select COUNT(*) from resumo where codCli = @codCli)

-- Verifica se existe no resumo
if @existeCli > 0
update resumo set valorTotal = valorTotal + @valor
where codCli = @codCli
else
insert into resumo (codCli, valorTotal) values (@codCli, @valor)

print 'Dados inseridos com sucesso!!!'
end
end
end

create table resumo (codCli int primary key, valorTotal decimal(10,2))

select * from ItensLocacao

select DATEADD(dd,3, GETDATE())

Exemplo do uso do WHILE

declare @contador int

set @contador = 1

while @contador < 15
begin
print @contador
set @contador = @contador + 1

-- Uso do BREAK
if @contador = 10
break
end

print 'Fim do Programa!!!!!'

Exemplo do uso do CASE

select func_nome, func_salario,
case
when func_salario >= 0 and func_salario <= 500 then 'Assalariado'
when func_salario > 500 and func_salario <= 2000 then 'Salário padrão'
when func_salario > 2000 then 'Cargo de confiança'
else 'Salário negativo!!!!???'
end as 'Situação'
from funcionarios

-- ou

select func_nome, func_salario,
case sexo
when 'M' then 'Masculino'
when 'F' then 'Feminino'
else 'Sei lá'
end as 'Sexo'
from funcionarios

-- Outro exemplo (não executa)

select codMidia, DescMidia,
case quantidade
when 0 then 'Poucas Unidade'
when in (1,2) then 'Quantidade Média'
when 4 then 'Muitas unidades'
else 'Sem opção'
end as 'QTD'
from Midias

-- Esse executa
select codMidia, DescMidia,
case
when quantidade = 0 then 'Poucas Unidade'
when quantidade >= 1 and quantidade <= 2 then 'Quantidade Média'
when quantidade > 2 then 'Muitas unidades'
else 'Sem opção'
end as 'QTD'
from Midias

Exemplo de Stored Procedures

create procedure sp_RetornaFunc as
select * from funcionarios

-- Para executar
exec sp_RetornaFunc

select * from sp_RetornaFunc -- dá erro

-- Outro exemplo
create procedure sp_Resumo as

declare @totalA int
declare @totalB int
declare @valorA decimal
declare @valorB decimal

set @totalA = (select COUNT(*) from funcionarios where func_salario < 1500)
set @totalB = (select COUNT(*) from funcionarios where func_salario >= 1500)
set @valorA = (select SUM(func_salario) from funcionarios where func_salario < 1500)
set @valorB = (select SUM(func_salario) from funcionarios where func_salario >= 1500)

print 'Nesta empresa trabalham ' + cast(@totalA as varchar) +
' funcionários que ganham um total de R$ ' +
cast(@valorA as varchar)

print 'Nesta empresa trabalham ' + cast(@totalB as varchar) +
' funcionários que ganham um total de R$ ' +
cast(@valorB as varchar)

exec sp_Resumo

Exemplo de SP com Parâmetros

create procedure sp_Soma (@n1 int, @n2 int) as
begin
declare @resultado int

set @resultado = @n1 + @n2
print 'A soma é: ' + cast(@resultado as varchar)
end

exec sp_Soma 1, 4

create procedure sp_AlugaFita (codLocacao int, codFita int, codCli int, valor double) as
begin
select codFita, codCli from