create table incontri
( 	CodLui varchar(16),
	CodLei varchar(16),
	DataIncontro datetime,
	citta varchar(20),
	Occasione char(10) check (Occasione IN ('cena','discoteca','spiaggia','montagna','altro')) DEFAULT('cena'),
	primary key (CodLui,CodLei,DataIncontro),
	constraint diversi check (codLui <> codLei),
        foreign key (CodLui) references CuoriSolitari(CodiceFiscale)
	on delete no action
	on update no action,
	foreign key (CodLei) references CuoriSolitari(CodiceFiscale)
	on delete no action
	on update no action
)


INSERT INTO Incontri VALUES('bdgcgdwer234hd89','ndhcgdwer234h459','03/05/2002','Genova','cena');
INSERT INTO Incontri VALUES('bdgcgdwer234hd89','ndjfyrieos9046hd','04/10/2002','Genova','spiaggia');
INSERT INTO Incontri VALUES('bdgcgdwer234hd89','ndjfyrieos9046hd','07/01/2003','Rapallo','cena');
INSERT INTO Incontri VALUES('mdldoeeer234hd89','ndjfyrieos9046hd','08/11/2003','Savona','discoteca');
INSERT INTO Incontri VALUES('mdldoeeer234hd89','ndjf0wweos9046hd','03/03/2003','Savona','cena');



create procedure IncontriDel @codCS varchar(16), @data datetime
as

DECLARE @Cognome VARCHAR(20)
DECLARE @nome VARCHAR(20)
DECLARE @eta NUMERIC

if (@codCS IN (select codiceFiscale 
    from Iscrizione
    where @data between  DataIscrizione and DataFineIscrizione))
  begin
      DECLARE c CURSOR FOR select distinct nome,cognome,eta 
	                   from CuoriSolitari, Incontri
	                   where (Cuorisolitari.CodiceFiscale=Incontri.CodLei and 
                              Incontri.CodLui=@codCS) or 
                              (Cuorisolitari.CodiceFiscale=Incontri.CodLui and 
                               Incontri.CodLei=@codCS)
      OPEN c
      FETCH NEXT FROM c INTO @Cognome, @Nome, @eta
      WHILE (@@FETCH_STATUS = 0)
	BEGIN
	  PRINT 'Cognome: ' + @Cognome + ' Nome: ' + @Nome + ' eta: ' + CAST(@eta AS VARCHAR(2))
	  FETCH NEXT FROM c INTO @Cognome, @Nome, @eta
	END
      CLOSE c
      DEALLOCATE c
  end
else 
  begin
    delete from Iscrizione where codiceFiscale=@codCS
    insert into Iscrizione values (@codCS,@data,'01/12/2010',200);
end
GO

exec IncontriDel @codcs='mdldoeeer234hd89', @data='04/10/2002'
GO