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.