use Projekt
GO
-- Check to see whether this stored procedure exists.
IF OBJECT_ID (N'usp_GetErrorInfo', N'P') IS NOT NULL
    DROP PROCEDURE usp_GetErrorInfo;
GO
-- Create procedure to retrieve error information.
CREATE PROCEDURE usp_GetErrorInfo
AS
    SELECT
    ERROR_NUMBER() AS ErrorNumber
    ,ERROR_SEVERITY() AS ErrorSeverity
    ,ERROR_STATE() AS ErrorState
    ,ERROR_LINE () AS ErrorLine
    ,ERROR_PROCEDURE() AS ErrorProcedure
    ,ERROR_MESSAGE() AS ErrorMessage;
GO

ALTER PROC spAddToTeam
    @teamname VARCHAR(30), @maNr INT
AS BEGIN
    SET NOCOUNT ON;
    IF NOT EXISTS (SELECT * FROM tblProjektteams WHERE teamname = @teamname)
    BEGIN
        BEGIN TRY
            BEGIN TRANSACTION
            INSERT INTO tblProjektteams VALUES (@teamname)

            COMMIT TRANSACTION;
        END TRY
        BEGIN CATCH
            EXEC usp_GetErrorInfo
            IF @@TRANCOUNT > 0
                ROLLBACK TRANSACTION;
        END CATCH;
        PRINT @@TRANCOUNT
        IF @@TRANCOUNT > 0
            COMMIT TRANSACTION;
    END
    BEGIN TRY
        BEGIN TRANSACTION
        INSERT INTO tblTeammitglieder VALUES ((SELECT TeamNr FROM tblProjektteams
            WHERE TeamName = @teamname), @maNr)
    END TRY
    BEGIN CATCH
        EXEC usp_GetErrorInfo
            IF @@TRANCOUNT > 0
                ROLLBACK TRANSACTION;
    END CATCH
    PRINT @@TRANCOUNT
    IF @@TRANCOUNT > 0
        COMMIT TRANSACTION;
END
GO

use Projekt
GO
EXEC spAddToTeam 'team', 492