Something like:

create function dbo.Conditional_Lookup

(

@vID int

)

RETURNS varchar(255)

as

BEGIN

declare @vOutput varchar(255)

declare @v1 varchar(255)

declare @v2 varchar(255)

select @v1 = IsNull(MyField, '') from MyTable where ID=@vID

if (@v1 = '')

begin

select @v1 = IsNull(MyField2, '') from MyTable where ID=@vID

end

...

set @vOutput = @v1

RETURN @vOutput

END

I'm not trying to reproduce your specific logic in this example function. The point being conveyed is that a scalar function can contain your complex conditional logic AND be part of your select list; i.e.,

select dbo.Conditional_Lookup(myTable.ID), myTable.Field2, etc.

If this still doesn't make sense, please take a few minutes to review scalar-functions.