begin tran

go
CREATE PROCEDURE CalcularNota @matricula int
AS
SET NOCOUNT ON

insert into media_aluno (ERROS, ACERTOS, NOTA, MATRICULA, DIFICULDADE)
select
	count(t2.resposta) AS ERROS,
	((count(t2.resposta) - 20)*-1) as ACERTOS,
	(CAST(((count(t2.resposta) - 20)*-1) AS float)/2) AS MÉDIA,
	t2.MATRICULA,
	(select NOME_DIF FROM tic where PARAM_DIF = (select
	case when min(param_dif) = min(param_dif)
		then  min(param_dif)
		else 15
	end as DIFICULDADE
from tic
where param_dif >= count(t2.resposta))) as DIFICULDADE
from questoes t1
inner join questoes_aluno t2 on (t1.questao = t2.questao)
where t1.resposta <> t2.resposta
and t2.MATRICULA = @matricula
group by t2.nome, t2.MATRICULA


GO

commit


execute CalcularNota 3