SQL Error handling

Question

Hi there,

Can you tell me if error handling is worth putting into this stored procedure?

-- **********************************************
-- Delete and create a new 'Truncate' function...
-- **********************************************

IF EXISTS(SELECT name 
          FROM   sysobjects 
          WHERE  name = 'truncate_description' AND 
                 type = 'FN')
  DROP FUNCTION truncate_description
GO

CREATE FUNCTION truncate_description(@description varchar(1000), @numchars int=500) RETURNS varchar(500)
BEGIN
  DECLARE @newDescription varchar(500);
  DECLARE @truncMessage varchar(33);
  SET @truncMessage = '..Click on title to read article.';

  IF (len(@description) >= @numchars)
    IF (substring(@description, @numchars, 1) = '.')
      SET @newDescription = @description;
    ELSE
    BEGIN
      SET @newDescription = substring(@description, 1, @numchars - len(@truncMessage));
      IF (charindex('.', @newDescription) > 0)
      BEGIN
        WHILE (substring(@newDescription, len(@newDescription), 1) <> '.')
        BEGIN
          SET @newDescription = substring(@newDescription, 1, len(@newDescription) - 1);
        END
      END
      SET @newDescription = @newDescription + @truncMessage;
    END
  ELSE
    SET @newDescription = @description;

  RETURN @newDescription;
END
GO


-- *********************************************
-- Delete and create a new 'Truncate' trigger...
-- *********************************************

IF EXISTS(SELECT name 
          FROM   sysobjects 
          WHERE  name = 'tr_tblProfile_truncateDescription' AND 
                 type = 'TR') 
  DROP TRIGGER tr_tblProfile_truncateDescription;
GO

CREATE TRIGGER tr_tblProfile_truncateDescription ON tbl_profile
AFTER INSERT, UPDATE AS
BEGIN
  UPDATE tbl_profile 
  SET description = dbo.truncate_description(i.description, 500)
  FROM tbl_profile p INNER JOIN inserted i on p.id=i.id
END
GO


-- ******************************************
-- Run the trigger on all existing records...
-- ******************************************

UPDATE tbl_Profile
SET description = description
GO

Many Thanks,

Answer 1

First of all - it's a function, not a stored procedure.

But error handling is always good - however a SQL function is probably not the best place to put it.

Considering this function works on a known schema and known data format (varchar) with known SQL functions (len, substring)... you should be able to unit-test this and consider any errors it may throw as extrinsic to the function itself.

It makes me lean towards an answer of no, don't handle errors here.

Answer 2

Implementing custom error handling within your T-SQL coding is a good if not essential practice in my opinion.

If the stored procedure/function that you are looking to develop is to form part of an applications middle tier, then I would recommend that you implement your own custom error handling.

By this I mean that you should raise errors that are specific to circumstances generated by your applications processes and log these in a generic error log table within the appropriate database.

An example of how to do this and incorporate it into the typical try catch block can be found within SQL Server Books Online.

Particularly the section titled: Error Handling Solution in the Adventure Works Database

http://msdn.microsoft.com/en-us/library/ms179296(SQL.90).aspx