SQL Server Performance

Odd UDF problem

Discussion in 'General Developer Questions' started by Chappy, Jul 11, 2003.

  1. Chappy New Member

    I have the following trivial UDF



    CREATE FUNCTION dbo.ascii_format_int (@i int)
    RETURNS char(12) AS
    BEGIN
    RETURN
    CONVERT(char(12), REPLICATE(' ', 12 - LEN(CONVERT(varchar(12), ISNULL(@i,'')))) +
    CONVERT(varchar(12), ISNULL(@i,'')))
    END

    When i call it once in query analyser, passing NULL, it returns a padded string as expected, but containing ' 0'.

    However, when I run the code outside a UDF, and replace @i with NULL, the function returns a padded string containing ' '. This is the behavior I want. Why doesnt it work inside a UDF ?
  2. vbkenya New Member

    The ISNULL expression is always deterministic when called within a UDF and therefore will return a value of the same data type (int) as the input expression. That is why you have to check for the NULL value in your UDF.

    Use this modification instead:

    ALTER FUNCTION dbo.ascii_format_int (@i int)
    RETURNS char(12) AS
    BEGIN
    DECLARE @retval char(12)
    IF @i is NULL
    SET @retval=REPLICATE(' ', 12)
    ELSE

    set @retval=CONVERT(char(12), REPLICATE(' ', 12 - LEN(CONVERT(varchar(12), ISNULL(@i,'')))) +
    CONVERT(varchar(12), ISNULL(@i,'')))
    RETURN @retval
    END



    Nathan H.O.
    Moderator
    SQL-Server-Performance.com
  3. Chappy New Member

    Thanks Nathan. I originally wrote it like this but tried using ISNULL because it didnt seem to perform too well. The solution Ive settled for is pretty much the same as yours



    CREATE FUNCTION dbo.ascii_format_int (@i int)
    RETURNS char(12) AS
    BEGIN
    DECLARE @s varchar(12)
    SELECT @s = CASE @i WHEN NULL THEN '' ELSE CONVERT(varchar(12), @i) END
    RETURN REPLICATE(' ', 12-LEN(@s)) + @s
    END

    Ive not yet done any quantifyable performance testing on these. It will be interesting to see if performance does differ, or if I was just imagining it.

Share This Page