Transact SQL Errors Appearing During Runtime Execution Only (Testing T/SQL Code After Conversion)

During conversion to SQL Server, you can figure out all syntax issues and successfully create objects such as stored procedures, functions, triggers in the SQL Server database without any errors.

Unfortunately, some issue still can appear when you try to execute the converted code.

You can create a procedure that accessing tables that do not exist at the current moment in SQL Server:

   CREATE PROCEDURE sp_select1
   AS 
   BEGIN 
    SELECT * FROM t_select
   END
 
   -- Command(s) completed successfully.

Error appears when you call this procedure:

   EXECUTE sp_select1
 
   -- Msg 208, Level 16, State 1, Procedure sp_select1, Line 4
   -- Invalid object name 't_select'.

In Oracle, you can also create such a procedure, but it will be created with a warning:

Then when you try to execute this procedure, Oracle returns:

You can successfully create the following procedure:

   CREATE PROCEDURE sp_cast1
   AS 
   BEGIN 
    PRINT 'San Francisco ' + 77;    
   END
 
   -- Command(s) completed successfully.

But when you call the procedure you get the following:

   EXECUTE sp_cast1
 
   -- Msg 245, Level 16, State 1, Procedure sp_cast1, Line 4
   -- Conversion failed when converting the varchar value 'San Francisco ' to data type int.

The correct procedure code:

   CREATE PROCEDURE sp_cast1
   AS 
   BEGIN 
    PRINT 'San Francisco ' + CAST(77 AS VARCHAR);    
   END
 
   -- Command(s) completed successfully.
 
   EXECUTE sp_cast1

• SQL Server White Papers, Articles and Reference