SQL Function problem

[Etienne_123]

Anyone have any ideas why this won't work? I just want to check whether some fields are not null before I set the value of @UserName

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = Object_ID('[dbo].[__Reporting_GetUserName]')
AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
DROP FUNCTION [dbo].[__Reporting_GetUserName]
END

BEGIN
EXEC dbo.sp_executesql N'
CREATE FUNCTION [dbo].[__Reporting_GetUserName] (@userId int)
RETURNS VARCHAR(MAX)
AS
BEGIN

DECLARE @UserName VARCHAR(MAX)

SET @UserName =
(
IF [FirstName] is not null
BEGIN
SELECT [FirstName] + '' '' + [LastName]
FROM __User
WHERE Id = @userId
END
)

RETURN @UserName
END
'
END

[PIEBALDconsult]

Why not use a CASE? SELECT CASE WHEN [FirstName] is not null THEN [FirstName] + '' '' + [LastName] ELSE null END FROM __User WHERE Id = @userId (Or something.)

[Lost User]

Etienne_123 wrote:

Anyone have any ideas why this won't work?

What would it fill the variable with if FirstName does equall null? You'd better assign it directly without the IF statement, and use the [ISNULL](http://msdn.microsoft.com/en-us/library/ms184325.aspx)[[^](http://msdn.microsoft.com/en-us/library/ms184325.aspx "New Window")] function.

Bastard Programmer from Hell :suss: