Problems with error handling...

• Derek Jander

  SSC Rookie

  Points: 38

  More actions

  September 21, 2005 at 9:33 am

  #591834

  I guess I don't know how to explain what I want to do that really good.. But Sushila, it seems like you've got it!

  I want to be able to handle the errors because if not I will have big problems to find out what went wrong when I execute my process that will call my stored procedure. Anyway, I will try now with using functions instead that returns 0 if everything went ok! And not treat the errors directly in my code... I'll let you know how it worked out...

• sushila

  SSC-Dedicated

  Points: 35293

  More actions

  September 21, 2005 at 9:34 am

  #591837

  Derek - don't know if you've gotten around to reading the articles yet or not but here's some help from the first one I listed:

  divide your t-sql into 2 separate procedures... the first one (we'll call it "Sproc1") will have this part of the code...

  DECLARE @intError INT
  DECLARE @Col01 varchar(100)
  DECLARE @Col02 varchar(100)
  DECLARE @Col03 varchar(100)
  DECLARE @Col04 varchar(100)
  DECLARE @Col05 varchar(100)
  
   insert into tArticulos(Articulo, Descripcion, UnidadBase, CosteEstandar)
   select Col02, Col03, convert(datetime, Col04), Col05
   FROM dbo.tNavisionBuffer Nav
   WHERE Col01 = '01' AND NOT EXISTS (Select * from dbo.tArticulos TA where TA.Articulo = Nav.Col01)
   
  Your second procedure (called "Sproc2") will be...
  
  CREATE PROCEDURE Sproc2 AS
  DECLARE @Error int
  
  EXEC Sproc1
  
  SET @Error = @@ERROR
  
  IF @Error  0
  BEGIN
  PRINT 'Error is ' + @Error
          insert into tDTSLogs(Fecha, Proceso, CodigoSQLServer, Descripcion, IDItem)
           values(GETDATE(), 'tArticulos',@Error,'Error tabla tArticulos', @Col02)
  END
  ELSE
  BEGIN
  ...do something else....
  END
  

  see if this works...

  
  
  
  
  
  
  **ASCII stupid question, get a stupid ANSI !!!**