CREATE PROCEDURE [dbo].al_applications_create
    @in_application_name      nvarchar(256),
    @in_application_id        uniqueidentifier OUTPUT
AS
BEGIN
    SELECT  @in_application_id = application_id FROM dbo.al_applications WHERE LOWER(@in_application_name) = lowered_application_name

    IF(@in_application_id IS NULL)
    BEGIN
        DECLARE @TranStarted   bit
        SET @TranStarted = 0

        IF( @@TRANCOUNT = 0 )
        BEGIN
            BEGIN TRANSACTION
            SET @TranStarted = 1
        END
        ELSE
            SET @TranStarted = 0

        SELECT  @in_application_id = application_id
        FROM dbo.al_applications WITH (UPDLOCK, HOLDLOCK)
        WHERE LOWER(@in_application_name) = lowered_application_name

        IF(@in_application_id IS NULL)
        BEGIN
            SELECT  @in_application_id = NEWID()
            INSERT  dbo.al_applications (application_id, application_name, lowered_application_name)
            VALUES  (@in_application_id, @in_application_name, LOWER(@in_application_name))
        END


        IF( @TranStarted = 1 )
        BEGIN
            IF(@@ERROR = 0)
            BEGIN
            SET @TranStarted = 0
            COMMIT TRANSACTION
            END
            ELSE
            BEGIN
                SET @TranStarted = 0
                ROLLBACK TRANSACTION
            END
        END
    END
END
GO
GRANT EXECUTE ON  [dbo].[al_applications_create] TO [swcmadmin]
GO