Sorry, I cannot post the actual code for security reasons. I have figured out a way to write a function that returns the value that I am looking for which works perfect. Scalar function was taking really long about 20s; then I converted to MultiLine table valued function. But this is also taking about 8s to return result which is about 1400 rows. Is there anyway to convert this to Inline TVF? I am hopeing Inline TVF would give some perfromance gain. Or any other suggestion to gain performance?

CREATE FUNCTION [dbo].[getInfo]

(

@a varchar(100)

)

RETURNS

@info TABLE

(

ReturnValue varchar(max)

)

AS

BEGIN

DECLARE @Var1 varchar(100), @Var2 varchar(100), @Var3 varchar(100), @Var4 varchar(100), @Var5 varchar(100)

DECLARE @ReturnVal1 varchar(100), @ReturnVal2 varchar(100), @ReturnVal3 varchar(100), @ReturnVal3 varchar(100), @ReturnVal5 varchar(100)

DECLARE @Result varchar(max)

SELECT @Var5 = max(mycol) FROM Mytable WHERE mycol = @a

SELECT@ReturnVal5 = RetVal, @Var4 = Val4 FROM Table5 WHERE Val5 = @Var5

SELECT @ReturnVal3 = RetVal, @Var3 = Val3 FROM Table4 WHERE Val4 = ISNULL(@Var4,@a)

SELECT @ReturnVal3 = RetVal, @Var2 = Val2 FROM Table3 WHERE Val3 = ISNULL(@Var3,@a)

SELECT @ReturnVal2 = RetVal, @Var1 = Val1 FROM Table2 WHERE Val2 = ISNULL(@Var2,@a)

SELECT @ReturnVal1 = RetVal FROM Table1 WHERE Val1 = ISNULL(@Var1,@a)

SET @Result = @ReturnVal1 + @ReturnVal2 + @ReturnVal3 + @ReturnVal3 + @ReturnVal5

INSERT INTO @info VALUES (@Result)

RETURN

END