Diese Vorlage ist sehr nützlich, weil
-
es wird die Ausführungszeit pro Schritt der Prozedur ausgegeben
-
es wird die Zahl der geänderten Zeilen pro Schritt ausgegeben
-
es wird die Gesamtzahl der geänderten Zeilen ins API Log geschrieben
-
stützt ein Schritt ab, wird der Schritte und der Grund korrekt ausgegeben
-
es wird eine REST_API ähnlicher Statuscode zurückgeben
-
es werden die Metadaten als extended Properties dokumentiert
-
Korrekt geloggte Fehlerbeispiele
-
Berechnungsfehler (z.B. Division durch 0)
-
Insert von NULL in Spalte die nicht NULL erlaubt
-
-
Nicht abgefangen werden folgende Fehler
-
Verwendung von nicht existierendem Objekt -> diesen Fehler kann TRY/CATCH nicht fangen (außer bei dynamischem SQL, was andere Nachteile hat)
-
muss selbst gefangen werden per IF OBJECT_ID (xxx) IS NOT NULL..
-
SQL
DROP PROCEDURE IF EXISTS control.spProzedur;
GO
/*
Prozedur um
1.
2.
3.
Gerd Tautenhahn for Saxess Software GmbH
Zuletzt modifiziert: 07/2026 for OCT 2026.05
Testcall Procedure
DECLARE @RC INT;
EXEC @RC = control.spProzedur
@Username = N'SQL'
,@DatenquellenID = N'GT'
PRINT @RC;
Testcall Result
SELECT * FROM integration.t*;
Testcall Basistabellen
SELECT * FROM staging.t*;
Testcall Log
SELECT TOP 5 * FROM system.tAPILog;
Testcall Documentation
SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'control', 'PROCEDURE', 'spProzedur',NULL,NULL)
UNION ALL
SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'control', 'PROCEDURE', 'spProzedur','PARAMETER',NULL)
*/
CREATE PROCEDURE control.spProzedur
@Username NVARCHAR(255)
,@DatenquellenID NVARCHAR(255)
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ProcedureName NVARCHAR(255) = OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID)
,@ParameterString NVARCHAR(MAX) = CONCAT (
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''','''
,ISNULL(@DatenquellenID ,N'NULL'), N''''
)
,@EffectedRows_Step INT = 0
,@EffectedRows_Total INT = 0
,@ResultCode INT = 501
,@TimestampCall DATETIME = CURRENT_TIMESTAMP
,@Comment NVARCHAR(4000) = N''
,@TransactUsername NVARCHAR(255) = N''
,@StartTime DATETIME2 = SysUTCDateTime()
,@Message NVARCHAR(4000) = N''
,@Arbeitsschritt NVARCHAR(255) = N'0. Initialisierung';
BEGIN TRY
BEGIN TRANSACTION;
-- ***********************************************************************************
-- Arbeitsschritt 1 - X und Y tun
-- ***********************************************************************************
SET @Arbeitsschritt = N'1a. X tun';
SELECT 1 AS X;
--SELECT 1/0 AS X;
SET @EffectedRows_Step = @@ROWCOUNT;
SET @EffectedRows_Total = @EffectedRows_Total + @EffectedRows_Step;
SET @Message = CONCAT(N'ENDE Schritt ',@Arbeitsschritt, ' Es wurden ', @EffectedRows_Step,N' Zeilen xxxxxxxxx (started after ', DateDiff(millisecond, @StartTime, SysUTCDateTime()),N'ms)');
EXEC system.spSEND_Message 'DEBUG', @Message;
SET @Arbeitsschritt = N'1b. Y tun';
SELECT 2 AS Y;
/*
DROP TABLE IF EXISTS #temp;
CREATE TABLE #temp
(
Zahl INT NOT NULL
);
INSERT INTO #temp
SELECT NULL AS Y
*/
SET @EffectedRows_Step = @@ROWCOUNT;
SET @EffectedRows_Total = @EffectedRows_Total + @EffectedRows_Step;
SET @Message = CONCAT(N'ENDE Schritt ',@Arbeitsschritt, ' Es wurden ', @EffectedRows_Step,N' Zeilen xxxxxxxxx (started after ', DateDiff(millisecond, @StartTime, SysUTCDateTime()),N'ms)');
EXEC system.spSEND_Message 'DEBUG', @Message;
-- #########################################################################################################
-- Arbeitschritt 2 - Z aufbereiten
-- #########################################################################################################
SET @Arbeitsschritt = N'2a. Z tun';
SELECT 3 AS Z;
--SELECT 3 FROM tNichtDa; -- das wird nicht gefangen, nur so wie folgt
/*
IF OBJECT_ID('tNichtDa') IS NOT NULL
SELECT 3 FROM tNichtDa;
ELSE
BEGIN
SET @Message = 'Object "tNichtDa" fehlt'
EXEC system.spSEND_Message 'ERROR', @Message;
END
*/
SET @EffectedRows_Step = @@ROWCOUNT;
SET @EffectedRows_Total = @EffectedRows_Total + @EffectedRows_Step;
SET @Message = CONCAT(N'ENDE Schritt ',@Arbeitsschritt, ' Es wurden ', @EffectedRows_Step,N' Zeilen xxxxxxxxx (started after ', DateDiff(millisecond, @StartTime, SysUTCDateTime()),N'ms)');
EXEC system.spSEND_Message 'DEBUG', @Message;
SET @ResultCode = 200;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
SET @Comment = ERROR_MESSAGE();
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
SET @ResultCode = 500;
SET @Message = CONCAT(N'ROLLBACK due to not executable command bei Arbeitsschritt: "',@Arbeitsschritt, '" Grund: ',@Comment);
EXEC system.spSEND_Message N'ERROR', @Message;
SET @Comment = @Message;
END CATCH
EXEC system.spPOST_APILogEntry @Username, @TransactUsername, @ProcedureName, @ParameterString, @EffectedRows_Total, @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.spProzedur TO [Username or rolename];
-- SET documentation variables ***********************************************************************
DECLARE
@level0name NVARCHAR(255) = N'control' -- enter schema name of the table
,@level1name NVARCHAR(255) = N'spProzedur' -- 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'REWE' -- 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'Aufbau der Tabellen X, Y, Z';
EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name;
-- optional SET parameter documentation (only for Core / Standardmodules)
SET @level2name = N'@DatenquellenID';
SET @value = N'ID der Datenquelle';
EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
GO