Prozedurvorlage um alle Products eines Templates in eine Tabelle zu materialisieren, es entsteht eine Tabelle control.t[Productcode]_TemplateTable_[Template]. Diese Prozedur steuert die Erstellung der Tabelle sehr exakt und datentypgerecht, es wird daher eine Prozedur pro Template angelegt und genau auf dieses zugeschnitten.
Funktionen
-
FactoryID, ProductlineID, ProductID werden als führende Spalten in der Tabelle angelegt
-
Productattribute können als Spalte definiert werden, darunter ein Spalte "Status" auf Productebene (Konvention, keine Bedingung)
-
eine Tabelle mit den WertreihenIDs als Spaltennamen, darunter eine Spalte "Aktiv" auf Wertreihenebene
Nutzen
-
die Tabelle kann in JOINs statt einer CTE verwendet werden - das ist wesentlich performanter
-
die Tabelle kann als Validierungsansicht auf ein Pivot Tab gelegt werden
-
die Tabelle kann materialisiert werden
-
per Prozeduraufruf in einem PivotTab
-
per Prozeduraufruf in einer beliebigen anderen Prozedur
-
per Pipelinestep
-
per PreSQL in einem PipelineStep
-
per Action (später auch direkt beim Speichern des Products, die Funktion “ActionOnSave” ist in Planung)
-
-
die Materialisierung erfolgt rein templebasiert - die Products können über beliebige Productlines / Factories verteilt sein, beliebige IDs und Namen haben
Zusatzinformationen
-
die Tabelle wird im Schema control erzeugt um die Anzahl der schemas gering zu halten (das ParamProduct 1.0 hatte ein Schema param, welches geschaffen wurde aufgrund des dynamischen SQLs und damit verbundener Berechtigungen, das ist hier nicht nötig)
Einrichtung
-
den String [Productcode] suchen + ersetzen durch einen passende String für die Produktfamilie, z.B. durch “REWE”
-
[Template] suchen und ersetzten durch den Templatenamen, welche all die Products haben, z.B. ZBJ , falls das Template ZBJ_VM heißt (somit ohne das Präfix VM etc.)
-
Zieltabellendeklaration auf die benötigten Spalten und Datentypen anpassen, ca. Zeile 69
-
Liste der Templatespalten in der Pivotierungsliste anpassen, ca. Zeile 164
-
Liste der Templatespalten in der SELECT Abfrage ergänzen, ca. Zeile 99 - ggf. auf passenden Typ casten
SQL
DROP PROCEDURE IF EXISTS control.sp[Productcode]_TemplateTable_[Template];
GO
/*
Prozedur um alle Products des Templates [Template] zu materialisieren, es entsteht eine Tabelle control.t[Productcode]_TemplateTable_[Template]
Gerd Tautenhahn for Saxess Software GmbH
Zuletzt modifiziert: 02/2026 for OCT 2026.05
Testcall
DECLARE @RC INT;
EXEC @RC = control.sp[Productcode]_TemplateTable_[Template]
@Username = 'SQL'
PRINT @RC
Testcall Result
SELECT * FROM control.t[Productcode]_TemplateTable_[Template];
Testcall API Log
SELECT TOP 5 * FROM system.tAPI_Log;
Testcall Documentation
SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'control', 'PROCEDURE', 'sp[Productcode]_TemplateTable_[Template]',NULL,NULL)
UNION ALL
SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'control', 'PROCEDURE', 'sp[Productcode]_TemplateTable_[Template]','PARAMETER',NULL)
*/
CREATE PROCEDURE control.sp[Productcode]_TemplateTable_[Template]
@Username NVARCHAR(255)
AS
BEGIN
DECLARE
@ProcedureName NVARCHAR(255) = OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID)
,@ParameterString NVARCHAR(MAX) = N'''' --set Strings inside in single quotes (N''','''), Numbers without strings inside without quotes (N','), end list with '''' in case of string or '' in case of number on last position
+ ISNULL(@Username ,N'NULL') + N''''
,@EffectedRows INT = 0
,@ResultCode INT = 501
,@TimestampCall DATETIME = CURRENT_TIMESTAMP
,@Comment NVARCHAR(2000) = N''
,@TransactUsername NVARCHAR(255) = N''
,@StartTime DATETIME2 = SysUTCDateTime();
-- Procedure specific declarations
DECLARE @Template NVARCHAR(50) = N'[Template]_VM';
-- NULL Protection for all Input parameters
IF @Username IS NULL SET @Username = N'';
-- START TRANSACTION ***********************************************************************************
BEGIN TRY
BEGIN TRANSACTION
-- check transaction user existence
SELECT @TransactUsername = system.fDetermineTransactionUsername (@Username);
IF @TransactUsername = N'403'
BEGIN
SET @ResultCode = 403;
RAISERROR('Transaction user don`t exists', 16, 10);
END;
-- Tabelle neu erzeugen
PRINT CONCAT(N'Zieltabelle neu erzeugen',N' (started after ', DateDiff(millisecond, @StartTime, SysUTCDateTime()),N'ms)');
DROP TABLE IF EXISTS control.t[Productcode]_TemplateTable_[Template];
CREATE TABLE control.t[Productcode]_TemplateTable_[Template]
(
FactoryID NVARCHAR(50) NOT NULL
,ProductLineID NVARCHAR(50) NOT NULL
,ProductID NVARCHAR(50) NOT NULL
-- Globalattribute
,ProductStatus NVARCHAR(50) NOT NULL
-- Wertreihen
,TimeID INT NOT NULL
,AKTIV NVARCHAR(50) NOT NULL
,DQ NVARCHAR(50) NOT NULL
,MDT NVARCHAR(50) NOT NULL
,KTO NVARCHAR(50) NOT NULL
,BKREIS NVARCHAR(50) NOT NULL
,KDIM1 NVARCHAR(50) NOT NULL
,KDIM2 NVARCHAR(50) NOT NULL
,KDIM3 NVARCHAR(50) NOT NULL
,ICP NVARCHAR(50) NOT NULL
,PER NVARCHAR(50) NOT NULL
,DATUM DATE NOT NULL
,BNR NVARCHAR(50) NOT NULL
,BETRAG MONEY NOT NULL
,BTEXT NVARCHAR(255) NOT NULL
,BEM NVARCHAR(255) NOT NULL
); -- Tabelle braucht keinen RowKey als Clustered Index, da immer neu erzeugt und keine Aufblähgefahr
PRINT CONCAT(N'Daten in Zieltabelle schreiben',N' (started after ', DateDiff(millisecond, @StartTime, SysUTCDateTime()),N'ms)');
INSERT INTO control.t[Productcode]_TemplateTable_[Template]
-- DECLARE @Template NVARCHAR(50) = N'[Template]_VM'; -- DEBUG Property für isolierte SELECT Ausführung
SELECT PivotTable.FactoryID
,PivotTable.ProductLineID
,PivotTable.ProductID
-- Globalattribute
,PivotTable.ProductStatus
-- Wertreihen
,PivotTable.TimeID
,COALESCE(PivotTable.AKTIV ,N'') AS AKTIV
,COALESCE(PivotTable.DQ ,N'') AS DQ
,COALESCE(PivotTable.MDT ,N'') AS MDT
,COALESCE(PivotTable.KTO ,N'') AS KTO
,COALESCE(PivotTable.BKREIS ,N'') AS BKREIS
,COALESCE(PivotTable.KDIM1 ,N'') AS KDIM1
,COALESCE(PivotTable.KDIM2 ,N'') AS KDIM2
,COALESCE(PivotTable.KDIM3 ,N'') AS KDIM3
,COALESCE(PivotTable.ICP ,N'') AS ICP
,COALESCE(
CAST(TRY_CAST(PivotTable.PER AS FLOAT)
AS INT)
,0) AS PER
,COALESCE(
calc.sfOCTDate2SQLDate(
CAST(TRY_CAST(PivotTable.DATUM AS FLOAT) AS INT)
)
,NULL) AS DATUM
,COALESCE(PivotTable.BNR ,N'') AS BNR
,COALESCE(
CAST(PivotTable.BETRAG AS MONEY)
,0
) AS BETRAG
,COALESCE(PivotTable.BTEXT ,N'') AS BTEXT
,COALESCE(PivotTable.BEM ,N'') AS BEM
FROM
( SELECT fV.FactoryID
,fV.ProductLineID
,fV.ProductID
,fV.TimeID
,fV.ValueSeriesID
,dP.Status AS ProductStatus
-- der Wert muss eine Spalte sein - verschiedene Typen müssen also zusammengeführt werden
,CASE
WHEN dVS.ISNUMERIC = 0 THEN ValueText
WHEN dVS.ISNUMERIC = 1 THEN CONVERT(NVARCHAR, CAST(ValueInt AS MONEY) / dVS.Scale, 2)
ELSE N'Error'
END AS Wert
FROM planning.tfValues fV
-- für Scale und Numeric
LEFT JOIN planning.tdValueSeries dVS ON
fV.ValueSeriesKey = dVS.ValueSeriesKey
-- für Template
LEFT JOIN planning.tdProducts dP ON
fV.ProductKey = dP.ProductKey
WHERE
fV.FactoryID <> N'ZT'
AND dP.Template = @Template
) AS SourceTable
PIVOT
(
MAX(Wert)
-- Liste der ValueSeriesIDs kann aus dem Template kopiert werden, dann nur Kommas ergänzen
FOR ValueSeriesID IN (AKTIV,DQ,MDT,KTO,BKREIS,KDIM1,KDIM2,KDIM3,ICP,PER,DATUM,BNR,BETRAG,BTEXT,BEM) -- Spaltennamen in [] setzten, falls Sonderzeichen, Leerzeichen oder Zahl am Anfang
) AS PivotTable
SET @EffectedRows = @@ROWCOUNT;
SET @ResultCode = 200;
PRINT CONCAT(N'Transaktion abgeschlossen',N' (after ', DateDiff(millisecond, @StartTime, SysUTCDateTime()),N'ms)')
COMMIT TRANSACTION
END TRY
-- START CATCH ***********************************************************************************
BEGIN CATCH
DECLARE @Error_state INT = ERROR_STATE();
SET @Comment = ERROR_MESSAGE();
ROLLBACK TRANSACTION
IF @Error_state <> 10
BEGIN
SET @ResultCode = 500;
PRINT 'Rollback due to not executable command.';
END
ELSE IF @ResultCode IS NULL OR @ResultCode/100 = 2
BEGIN
SET @ResultCode = 500;
END;
END CATCH
EXEC system.spPOST_APILogEntry @Username, @TransactUsername, @ProcedureName, @ParameterString, @EffectedRows, @ResultCode, @TimestampCall, @Comment;
RETURN @ResultCode;
END
GO
-- GRANT Rights (only need if end user need direct database access to this object)
-- GRANT EXECUTE ON OBJECT :: control.sp[Productcode]_TemplateTable_[Template] TO [Username or rolename];
-- SET documentation variables ***********************************************************************
DECLARE
@level0name NVARCHAR(255) = N'control' -- enter schema name of the table
,@level1name NVARCHAR(255) = N'sp[Productcode]_TemplateTable_[Template]' -- enter procedure name
,@SX_Owner NVARCHAR(255) = N'OCT.modules' -- enter owner name of the procedure from list (OCT.core, OCT.modules, Custom)
,@SX_Module NVARCHAR(255) = N'[Productcode]' -- enter module name as free text (CORE,FIN, DEBKRED, HR, ...)
,@SX_ShipmentFlag INT = 1 -- 0 = Demo object - out of shipment process
-- STANDARD OBJECTS
-- 1 = shiped from saxess standard without modification
-- 2 = shiped from saxess standard modified FOR customer from saxess
-- 3 = shiped from saxess standard modified FOR customer from partner
-- 4 = shiped from saxess standard modified FROM customer themself for own needs
-- CUSTOM OBJECTS
-- 10 = shiped from saxess as customer specific object
-- 11 = shiped from partner as customer specific object
-- 12 = shiped from customer as own specific object
,@SX_UserHint NVARCHAR(2000) = N''; -- optional, fill if Procedure shall be offerend for end user (e.g. for Pivot / Datagrid usage)
-- KEEP this default constants *************************************************************************
DECLARE
@name NVARCHAR(255) = N'MS_Description'
,@level0type NVARCHAR(255) = N'SCHEMA'
,@level1type NVARCHAR(255) = N'PROCEDURE'
,@level2type NVARCHAR(255) = N'PARAMETER'
,@level2name NVARCHAR(255) = N''
,@value NVARCHAR(1000) = N'';
SET @value = @SX_Owner;
EXEC sys.sp_addextendedproperty N'SX_Owner' ,@value,@level0type,@level0name,@level1type,@level1name;
SET @value = @SX_Module;
EXEC sys.sp_addextendedproperty N'SX_Module' ,@value,@level0type,@level0name,@level1type,@level1name;
SET @value = @SX_ShipmentFlag;
EXEC sys.sp_addextendedproperty N'SX_ShipmentFlag' ,@value,@level0type,@level0name,@level1type,@level1name;
SET @value = @SX_UserHint;
EXEC sys.sp_addextendedproperty N'SX_UserHint' ,@value,@level0type,@level0name,@level1type,@level1name;
-- SET documententation *************************************************************************
-- SET Procedure documentation
SET @value = N'Prozedur um die Products des Templates [Template] in eine gemeinsame Tabelle zu materialisieren.';
EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name;
GO