An welcher Stelle in einer Stored Procedure kann man OUTPUT-Parameter zurückgeben

Question

Hallo,

Ich möchte in einer SQL-Server-Datenbank mit SQL-Server Management 2012 eine Stored Procedure anlegen, die einen bestimmten Datensatz aus einer Tabelle kopiert.

Diese Tabelle besitzt einen eindeutigen Schlüssel namens InstanceID, der beim INSERT nicht mitgegeben werden muss, da diese Spalte automatisch inkrementiert wird.

Nach dem INSERT möchte ich die neue InstanceID aber suchen und an den Aufrufer mittels OUTPUT-Parameter zurückgeben.

Da wir mit Transactions arbeiten (die man notfalls wieder zurücksetzen kann) stellt sich für mich die Frage, an welcher Stelle in meiner Stored Procedure ich den OUTPUT-Parameter zurückgeben muss.

USE OneWhoIsWho;

IF OBJECT_ID('dbo.CopyInstance') IS NULL -- Check if SP Exists
    EXEC ('CREATE PROCEDURE dbo.CopyInstance AS SET NOCOUNT ON;') -- Create dummy/empty SP
GO

ALTER PROCEDURE dbo.CopyInstance
@instanceId int,
@txtInstanceDescription nvarchar(100),
@iHidden int,
@newInstanceId int OUTPUT
AS
BEGIN
	BEGIN TRANSACTION T1

	BEGIN TRY

		INSERT INTO [dbo].[Instance]
			([txtInstanceDescription]
			,[iHidden])
		VALUES
			(@txtInstanceDescription
			,@iHidden)

		SELECT @newInstanceId = MAX([dbo].[Instance].[InstanceID])
		FROM dbo.Instance
		WHERE dbo.Instance.txtInstanceDescription = @txtInstanceDescription
		AND dbo.Instance.iHidden = @iHidden

		--return @newInstanceId

        COMMIT TRANSACTION T1
    END TRY

    BEGIN CATCH
            ROLLBACK TRANSACTION T1
    END CATCH
END;
GO

Answer 1

Hallo Patrick,

Bei einer Prozedur an jeder Stelle, solange OUTPUT beim Parameter angegeben ist und dies auch beim Aufruf berücksichtigt. Der Wert wäre mit SET zu setzen und oben nach dem COMMIT. Den Rückgabe Wert von return sollte man nicht verwenden, da a) immer ein integer und b) per Konvention für den Fehlerstatus reserviert.

Wobei es OUTPUT Klausel auch für INSERT, UPDATE, DELETE gibt, was hier sinnvollerweise auf Deine INSERT Anweisung angewendet werden sollte. Denn Dein derzeitiges SELECT wäre nur  mit der Isolationsstufe Serializable multiuser fähig. Und ganz nebenbei ist OUTPUT deutlich schneller.

Bei Transaktionen sollte man immer @@TRANCOUNT prüfen, falls die Transaktion vorher abgebrochen wurde. Siehe Dokumentationsbeispiele zu TRY...CATCH. Andersrum: Sie hier zu benennen ist wenig sinnvoll.

Gruß Elmar

Answer 2

Hallo Elmar,

Danke für Deine Tipps.

Ich habe die StoredProcedure inzwischen ein wenig angepasst und so scheint es ganz gut zu gehen.

¬ USE [OneWhoIsWho]
GO
/****** Object:  StoredProcedure [dbo].[sp_CopyInstance]    Script Date: 31.05.2016 15:01:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_CopyInstance]
@instanceId int,
@txtInstanceDescription nvarchar(100),
@iHidden int,
@newInstanceId int OUTPUT
AS
BEGIN
	BEGIN TRANSACTION T1

	BEGIN TRY

		INSERT INTO [dbo].[Instance]
			([txtInstanceDescription]
			,[iHidden])
		VALUES
			(@txtInstanceDescription
			,@iHidden)

		--SELECT @newInstanceId = MAX([dbo].[Instance].[InstanceID])
		--FROM dbo.Instance
		--WHERE dbo.Instance.txtInstanceDescription = @txtInstanceDescription
		--AND dbo.Instance.iHidden = @iHidden

		SELECT @newInstanceId = SCOPE_IDENTITY()
		SELECT @newInstanceId AS InstanceID

        COMMIT TRANSACTION T1
    END TRY

    BEGIN CATCH
        ROLLBACK TRANSACTION T1
    END CATCH
END;

Answer 3

Hallo Patrick,

etwas "getweakte" Version der Prozedur:

-- Mit sp sollten nur Systemprozeduren beginnen, 
-- besser usp oder Hinweis auf die Art wie hier Insert/Update/Delete
CREATE PROCEDURE [dbo].[usp_CopyInstance]
	@instanceId int,
	@txtInstanceDescription nvarchar(100),
	@iHidden int,
	@newInstanceId int OUTPUT
AS
	-- http://www.sommarskog.se/error_handling/Part1.html#jumpXACT_ABORT
	SET XACT_ABORT, NOCOUNT ON;

	-- ohne explizites BEGIN TRANSACTION
	-- denn EF startet selbst eine Transaktion,
	-- und eine einzelne Anweisung ist im Autocommit Modus für sich atomar.
	-- https://msdn.microsoft.com/de-de/library/ms174377.aspx
	
	INSERT INTO [dbo].[Instance] (
		[txtInstanceDescription]
		, [iHidden])
	VALUES (
		@txtInstanceDescription
		, @iHidden);
	IF @@ERROR <> 0
	BEGIN
		SET @newInstanceId = SCOPE_IDENTITY();

		-- wenn nur fürs EF weglassen, da unnötiger Netzwerkverkehr
		SELECT @newInstanceId AS InstanceID;

		-- Nicht ausgewert beim EF, aber gute Konvention
		-- https://msdn.microsoft.com/de-de/library/ms174998.aspx
		RETURN 0;		
	END ELSE BEGIN
		RETURN 1;
	END;
GO

Die Transaktionsanweisungen habe ich weggelassen, da bereits das Entity Framework eine anfängt, siehe Working with Transactions (EF6 Onwards). Desweiteren siehe Links inline.

Gruß Elmar

Answer 4

Hallo Elmar,

Nochmals vielen Dank.

Ich habe ehrlich gesagt erst letzte Woche mit ASP.NET und Stored Procedures begonnen, bin also noch ein Frischling.

Deine Hinweise in der Überarbeitung finde ich sehr gut.

Vor allen Dingen das Error Handling ist ein Thema, das mir seit ein paar Tagen unter den Fingernägeln brennt.

Die Stored Procedures haben zwar bisher keine Fehler produziert aber ein gutes Error Handling ist nie verkehrt.

Eine Kleinigkeit ist mir bei @@ERROR noch aufgefallen. Es müsste an dieser Stelle natürlich heißen: IF @@ERROR=0