How do I get back the id of the new record created in a table using cursor and Procedure in SQL Server?

Question

I’m trying to make my previous run and return the registration ID that has just been created, as I don’t have much knowledge of cursors and procedures I don’t know where I am failing.

When I run the program I get the message (Message 3903, Level 16, Status 1, Procedure spClonaPedido, Line 59 The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.)

Below follows the way I am calling the Code and the Code, can anyone help me and explain to me what is going on? I built this code by searching the MS documents and had some tips.

The way I call Procedure:

'''

USE [BANCO]
GO

DECLARE @return_value int,
    @NovoId int

EXEC    @return_value = [dbo].[spClonaPedido]
    @PedidoId = 87163,
    @NovoId = @NovoId OUTPUT

SELECT  @NovoId as N'@NovoId'

SELECT  'Return Value' = @return_value

GO

'''

Below is the code of the trial:

'''

USE [BANCO]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spClonaPedido]
    -- Add the parameters for the stored procedure here
    @PedidoId int,
    @NovoId int output
AS
BEGIN TRY
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    SET CURSOR_CLOSE_ON_COMMIT ON;

    DECLARE @COLUNA VARCHAR(100), @COLUNAS NVARCHAR(MAX), @CLONAR NVARCHAR(MAX), @vInsertInto NVARCHAR(MAX);

    --- Inicializar a variavel
    SET @COLUNAS    = '';
    SET @CLONAR     = '';

    --sempre declare que o cursor é do tipo LOCAL FAST_FORWARD.
    --Local diz que é o scopo da execuçao e FAST_FORWARD significa que ele so vai pra frente.
    DECLARE CURCOLUNAS CURSOR LOCAL FAST_FORWARD FOR SELECT COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE   TABLE_CATALOG   = 'BANCO'
            AND TABLE_SCHEMA= 'dbo'
            AND TABLE_NAME  = 'TABELA'
    ORDER BY ORDINAL_POSITION
    OPEN CURCOLUNAS
        FETCH NEXT FROM CURCOLUNAS INTO @COLUNA
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @COLUNAS = @COLUNAS + ',' + @COLUNA
            FETCH NEXT FROM CURCOLUNAS INTO @COLUNA --- O into nesta linha
        END

        SET @COLUNAS= Right(@COLUNAS, len(@COLUNAS)-10)
        SET @vInsertInto = N'INSERT INTO TABELA ('+@COLUNAS+') SELECT '+@COLUNAS+' FROM TABELA WHERE PedidoId = '+@PedidoId;

        --SET IDENTITY_INSERT [BANCO].[dbo].[TABELA] ON
        BEGIN TRANSACTION;
            --EXECUTE sp_executesql @CLONAR;
            EXECUTE sp_executesql @vInsertInto;
            SET @NovoId=SCOPE_IDENTITY();
            RETURN  @NovoId;
        COMMIT TRANSACTION;
        --SET IDENTITY_INSERT [BANCO].[dbo].[TABELA] OFF
    CLOSE CURCOLUNAS;
    DEALLOCATE CURCOLUNAS;
End TRY

BEGIN CATCH
    ROLLBACK TRANSACTION;
END CATCH

'''

Answer 1

$Pdo = Connect::getInstance();

$userid = $Pdo->lastInsertId();

$Pdo->query (" INSERT INTO users_address (user_id, street) VALUES ('{userid}', 'Cariru Street'); ");