Odd UDF problem

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 ?

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

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.