use AdventureWorks2012 ;

---------------------------------------------------------------------------------
-- Abschnitt 11.6.3
declare @retFindReports TABLE (
    empid nchar(5) primary key,
    empname nvarchar(50) NOT NULL,
    mgrid nchar(5),
    title nvarchar(30) 
) ;

insert @retFindReports
	values ( '7654', 'Duck Donald', '', '' );

update @retFindReports
	set title = 'mr'
	where empid = '7654';

select *
	from @retFindReports ;

delete @retFindReports ;	
begin tran

declare @retFindReports TABLE (
    empid nchar(5) primary key,
    empname nvarchar(50) NOT NULL,
    mgrid nchar(5),
    title nvarchar(30) 
) ;

	
insert @retFindReports
	values ( '7654', 'Duck Donald', '', '' );

update @retFindReports
	set title = 'mr'
	where empid = '7654';

rollback

select *
	from @retFindReports ;

---------------------------------------------------------------------------------
-- Abschnitt 11.7.1
declare crsKundenHeuer cursor
	fast_forward for
		select distinct KundenNr
			from datKunden as k inner join
				datAufträge as a on
					k.Nr = a.KundenNr
			where year (Datum) = year(getdate());

open crsKundenHeuer;

declare @KNr int;
fetch next crsKundenHeuer into @KNr;
while @@fetch_status = 0
	begin 
	--hier irgendeine Bearbeitung
	fetch next crsKundenHeuer INTO @Knr;
	end

close crsKundenHeuer;
deallocate crsKundenHeuer;
go

---------------------------------------------------------------------------------
-- Abschnitt 11.9.1
/*
 * Erfasst die angegebenen Daten als weitere Kreditkarte.
 * 20141220 mw
 */
alter procedure addCreditCard
	@CustomerID int ,
	@CardType varchar (30) ,
	@CardNumber char(14) ,
	@ExpMonth tinyint ,
	@ExpYear smallint ,
	@CreditCardID int output
as 
	begin
	
	-- Im Sinne einer defensiven Programmierung stellen wir
	-- eingangs fest, ob die Operation gelingen kann.

	-- Am Ende werden wir dem Aufrufer mitteilen, ob die Operation
	-- geklappt hat (@rc=0) oder ob etwas schief gegangen ist. Die
	-- Bedingungen sind sehr willkürlich und sollen lediglich eine
	-- Arbeits*weise* demonstrieren:

	declare @rc int = 0,
			@flag int = 1;

	-- Ggf. Blanks vorne und hinten entfernen
	set @CardType = ltrim(rtrim(@CardType));

	-- 1: Person muss existieren
	-- ( Das würde selbstverständlich auch die Referenzielle
	-- Integrität automatisch überprüfen! )
	if not exists ( 
		select * 
			from Person.Person
			where BusinessEntityID = @CustomerID )
		begin
		set @rc += @flag ;
		end

	set @flag *= 2;

	-- 2: Neuer Kartentyp wird nicht akzeptiert
	if not exists ( 
		select * 
			from Sales.CreditCard 
			where CardType = @CardType )
		begin
		set @rc += @flag ;
		end

	set @flag *= 2;

	-- 4: Karte darf noch nicht vorhanden sein
	if exists ( 
		select * 
			from Sales.CreditCard 
			where CardNumber = @CardNumber )
		begin
		set @rc += @flag ;
		end

	set @flag *= 2;

	-- 8: Karte muss gültig sein
	if  @ExpYear <= year(getdate())
		begin
		if @ExpMonth < month(getdate())
			begin
			set @rc += @flag ;
			end
		end
	set @flag *= 2;
	
	if @rc != 0
		begin
		-- In raiserror darf kein String-Ausdruck stehen, 
		-- daher eine Variable:
		declare @msg varchar(max) = 
			'addCreditCard: Karte kann nicht eingefügt werden. '+
			'Fehlerflags: ' + cast ( @rc as varchar(20) ) ;
		raiserror ( @msg , 15, 1 ) ;
		return @rc ;
		end

	-- Hier: alles ist überprüft, wir kommen gar nicht zu dieser
	-- Stelle, wenn etwas nicht i.O. wäre

	begin tran

	declare @err int = null;

	set @flag *= 2;

	insert into Sales.CreditCard ( 
			CardType, CardNumber, ExpMonth, ExpYear )
		values ( @CardType, @CardNumber, @ExpMonth, @ExpYear ) ;
	-- Nur(!) so kann vergebene ID /und/ Fehlercode abgefangen
	-- werden:
	select @CreditCardID = @@identity, @err = @@error;
	-- Die @CreditCardID wird als Referenzparameter an den Aufrufer
	-- zurück übergeben.

	if @err != 0
		begin
		set @msg = 
			'addCreditCard: Einfügeoperation bei Karte ' +
			'gescheitert. Fehler: ' + cast ( @err as varchar(20) ) ;
		raiserror ( @msg, 15, 2 ) ;
		rollback ;
		return @flag ;
		end

	set @flag *= 2;

	insert into Sales.PersonCreditCard ( 
			BusinessEntityID, CreditCardID )
		values ( @CustomerID, @CreditCardID) ;
	select @err = @@error;

	if ( @err != 0 )
		begin
		set @msg = 
			'addCreditCard: Einfügeoperation bei Zuordnung ' +
			'gescheitert. Fehler: ' + 
			cast ( @err as varchar(20) ) ;
		raiserror ( @msg, 15, 3 ) ;
		rollback ;
		return @flag ;
		end

	commit

	return 0 ;
	
	end
go

declare @ID int = null;
exec addCreditCard @CustomerID = 11734, 
		@CardType = 'SuperiorCard' , 
		@CardNumber = '1234567890ABCD' ,
		@ExpMonth = 12 , @ExpYear = 2015 ,
		@CreditCardID = @ID output ;
print @ID ;
go

---------------------------------------------------------------------------------
-- Abschnitt 11.9.2
/*
 * Berechnet das Produkt für das Gewinnspiel.
 * 20141220 mw
 */
create procedure QtyProduct
	@OrderID int
as
	begin

	-- Datansatzzähler für Rest der Prozedur unterdrücken
	set nocount on ;

	-- Daten in eine Temporärtabelle bringen
	select OrderQty
		into #qty
		from Sales.SalesOrderDetail as d
		where SalesOrderID = @OrderID and
			OrderQty > 1 ;

	-- Dort müssen sie Datensatz für Datensatz angefasst werden,
	-- also Cursor
	declare qCursor cursor fast_forward for
		select OrderQty from #qty ;

	open qCursor;

	-- Das Produkt aller Zahlen
	declare @prod int = 1;
	-- Eine Variable für den jeweils aktuellen Datensatz
	declare @qty int ;

	-- Datensatz aus Cursor lesen
	fetch next from qCursor into @qty;
	while ( @@fetch_status = 0 )
		begin
		-- Produkt errechnen
		set @prod *= @qty ;
		-- Nächsten Datensatz lesen (sonst Endlosschleife!)
		fetch next from qCursor into @qty;
		end

	-- Alles schließen
	close qCursor;
	deallocate qCursor;
	drop table #qty;

	-- Ergebnis eintragen:
	-- Ein Null im Kommentar durch Leerstring ersetzen…
	update Sales.SalesOrderHeader
		set comment = ''
		where SalesOrderID = @OrderID and
			comment is null;

	-- …damit das hier funktioniert:
	update Sales.SalesOrderHeader
		set comment += cast ( @prod as varchar(20) )
		where SalesOrderID = @OrderID ;

	return 0 ;

	end
go

exec QtyProduct 43659 ;

select * from Sales.SalesOrderHeader where SalesOrderID = 43659 ;
go
