SQL Server Performance

exception handling in UDF

Discussion in 'SQL Server 2005 General Developer Questions' started by shankbond, Jan 9, 2010.

  1. shankbond New Member

    Hi,
    I have a problem here, I need to do some exception handling in the UDF
    CREATE FUNCTION dbo.testing (@var INT)
    RETURNS VARCHAR(6)
    WITH SCHEMABINDING
    AS
    BEGIN
    RETURN 'SS'+CONVERT(VARCHAR(4),@var);
    END
    DECLARE @d INT
    SET @d=5634
    SELECT dbo.testing (@d)
    This code will run fine if @d is less than 9999 but whenever it exceeds, I want to raiseerror and also log the error in a table, but unfortunately try catch blocks cannot be implemented in UDF.It(Try-Catch) can only be used in the calling context.
    http://technet.microsoft.com/en-us/library/cc505854.aspx
    Here is the table structure which is logging the error:
    CREATE TABLE [dbo].[Error_Log](
    [ErrorNumber] [int] NULL,
    [ErrorSeverity] [int] NULL,
    [ErrorState] [int] NULL,
    [ErrorProcedure] [varchar](300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [ErrorLine] [int] NULL,
    [ErrorMessage] [varchar](8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [TransactionCount] [int] NULL,
    [TimeEntry] [datetime] NOT NULL CONSTRAINT [DF_Error_Log_TimeEntry] DEFAULT (getdate())
    ) ON [PRIMARY]
  2. moh_hassan20 New Member

    One of the best practice in programming, is that the calling program is responsible for checking the validity/null values for the parameters that will be passed to the called function /procedure
    the function /procedure is responsible to check its internal business logic and fire exceptions as needed , not checking the validity of its parameters

    so

    case1: you run as a patch in QA
    DECLARE @d INT
    SET @d=5634
    if @d <= 9999
    SELECT dbo.testing (@d)
    else
    print "value should be less tan 9999"


    case2: you call the function from within GUI application, e.g c#

    you can check parameters before calling the function and fire exception as needed
  3. shankbond New Member

    [quote user="moh_hassan20"]One of the best practice in programming, is that the calling program is responsible for checking the validity/null values for the parameters that will be passed to the called function /procedure
    the function /procedure is responsible to check its internal business logic and fire exceptions as needed , not checking the validity of its parameters[/quote] Thanks for the reply,
    There is one more thing that I forgot to tell You that this function is used in a computed column as:
    CREATE TABLE dbo.testtable
    (
    columna INT IDENTITY(1,1),
    columnb AS (dbo.testing(columna))
    );

    SET IDENTITY_INSERT dbo.testtable ON
    GO
    INSERT INTO dbo.testtable (columna) VALUES ('2')
    INSERT INTO dbo.testtable (columna) VALUES ('4')
    INSERT INTO dbo.testtable (columna) VALUES ('56989')

    SELECT * FROM dbo.testtable;
    Here whenever the columna value exceedds the maximum value then SS* would be returned instead, which is a problem (it should have given an error or warning)
    Is there any work around with the problem?
    Any help shall be appreciated!
  4. moh_hassan20 New Member

    As the function is used in a computed column , so exceptions has no meaning, because exceptions should be handled by application /user.
    you can make a patch to search for the vlues "SS*" , and take corrective acction to modify data
    re-write the insert as:
    if @d <= 9999
    INSERT INTO dbo.testtable (columna) VALUES (@d)
    else
    -- insert the value into log error table to be corrected latter
    Can you tell me ,what the exception handling logic that you want to be applied?
  5. Adriaan New Member

    If you want to log errors, you won't be able to do it in a UDF - no inserts/updates/deletes allowed.
    Why not let a trigger handle this?
    And if you know beforehand that your data will at some point pass the 9,999 mark, then why not plan ahead and use a larger number of digits?
  6. shankbond New Member

    Hi Adriaan,
    [quote user="Adriaan"]Why not let a trigger handle this?[/quote]
    I have already put that limit to a very high limit but I still was curious to know that.
    Can You please explain the possibility with the trigger.
  7. Adriaan New Member

    Instead of the computed column, make it a regular CHAR or VARCHAR column. Then add an insert trigger that writes the calculated value into the column.
    Or if you're combining the value from the identity column with initials, you could also just let the client application handle that.

Share This Page