Return a value from a Function

[Kusno]

Dear all,
Sorry, if this Thread is wrong room :$ .

I want to return a field value from a table, so I created a function like this :

CREATE FUNCTION dbo.CheckStatusTrx(@TransNo Varchar(20))
RETURNS varchar(20)
AS
BEGIN
   DECLARE @Flag Numeric(1)
   SELECT @Flag = Flag, CreatedBy, ApprovedBy FROM TrxTD WHERE TransNo = @TransNo
   IF @Flag = 2 return 'REJECTED'	   	
END

but, I got this error :

Msg 141, Level 15, State 1, Procedure CheckStatusTrx, Line 6
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

Help me, what should I do?

Thanks