Proc Throws Error When Used with Do End

Proc throws error when used with do end

Your first code is interpreted as:

run(Proc.new) do |env|
  ...
end

and the block is is passed to run instead of new. The problem can be solved by doing:

run(Proc.new do |env|
  ...
end)

Error creating procedure: declare and end error

Sample syntax:

create or replace procedure insert_movimientos
    ( insertmov_cod_banco in number
    , insertmov_cod_sucur in number
    , insertmov_num_cta   in number
    , insertmov_fecha_mov in date
    , insertmov_tipo_mov  in varchar2
    , insertmov_importe   in number )
is
    sql_str varchar2(500) := 'INSERT INTO MOVIMIENTOS (';
begin
    dbms_output.put_line(sql_str);
end;
/

The trailing slash is for the client application so may not be required depending on what tool you are using.

I changed the datatype for insertmov_tipo_mov. char adds blank spaces which nobody needs, and is provided mainly for ANSI compatibility. It's best to use the standard type.

Even better, use table name.columnname%type to anchor it to the type of the corresponding table column.

A lot of examples online are in uppercase, for no clear reason. You don't as a rule write computer code in uppercase.

MS-SQL stored procedure throwing error after adding a new Join statement. Msg 102, Level 15, State 1, Line 279 Incorrect syntax near 'WHERE'

Query was getting truncated because of lengthy code.
Adding a new variable and assigning the last query of the sp to new variable resolved the issue.

sql3= 'SELECT * FROM LocationResults WHERE RowNumber BETWEEN 1 and 5
'

SQL Server stored procedure throws multiple errors

Errors with a severity of 11 or higher that occur during batch execution are returned to the application as a SqlException when the exception is raised by the SqlClient API. The individual errors are returned in the SqlException.Errors collection with the SqlException.Message containing the concatenated text of the individual errors of the collection.

Some SQL Server errors will terminate the batch so no subsequent statements in the batch are executed after the errors. In that case, only errors that happened before the batch terminating error are returned. All errors will be returned when multiple errors occur and none are batch terminating. So, depending on the specific error, the subsequent RAISERROR might not be executed.

Keep in mind that the default behavior is that SQL Server will continue to execute T-SQL statements in both your inner and outer procedures of no batch-terminating errors occur, including RAISERROR with severity 16. This could result in multiple errors returned.

Usually one doesn't want to continue batch execution after errors and raise a single error. This can be avoided by one or more of the following techniques:

• Use Structured error handling (TRY/CATCH) and use THROW instead of RAISERROR

• Check @@ERROR after each statement along with control flow statements

• Specify SET XACT_ABORT_ON so that non batch terminating errors are promoted to batch-terminating errors and transaction is rolled back

SET XACT_ABORT_ON is a best practice in stored procedures with explict transactions to ensure the transaction is rolled back in the event of a client timeout or query cancel. The batch will also be terminated unless a TRY/CATCH block is in scope.

In your case, I suggest you add a TRY/CATCH block to the outer proc. That CATCH block will be executed following an error in either the inner or outer proc. The CATCH block code can then raise the original error using THROW (SQL Server 2012 and later) or a user-defined error with RAISERROR severity 11+ to raise the exception in the client application. Below is an example.

CREATE PROCEDURE [dbo].[up_kod_text_save]
    -- parameters here
AS
SET NOCOUNT ON;
BEGIN TRY
    -- code here
END TRY
BEGIN CATCH --catch block will be entered after an error in either proc
    IF @@TRANCOUNT > 0 ROLLBACK; --needed only if BEGIN TRAN is used
    THROW; --this will raise the original error
END;
GO

SQL Server catch error from extended stored procedure

You can only test the result from an extended stored proc, and use that to throw an exception.

...
EXEC @rtn = dbo.xp_somethingCool
IF @rtn <> 0
    RAISERROR ...
...

In very simple terms, an extended stored proc is not SQL run by the database engine so you can't issue RAISERROR. See KB 190987 for some more info

---

Related Topics