MS SQL

MS SQL RSS

Return a value from a Function

Please support our MS SQL advertiser: Intel Parallel Studio Home

Thread Solved

•

Join Date: Aug 2007

Posts: 176

Reputation: Kusno is an unknown quantity at this point

Solved Threads: 13

Kusno

Offline

Junior Poster

Return a value from a Function

Jul 23rd, 2009

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 :

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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
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 :

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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.
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

NEVER NEVER NEVER GIVE UP

•

Join Date: Jul 2009

Posts: 2

Reputation: shijobaby has a little shameless behaviour in the past

Solved Threads: 0

shijobaby

Offline

Newbie Poster

Re: Return a value from a Function

Aug 21st, 2009

Hi

Actually this in the group of small errors consuming time

Just have aook on my blog

http://sqlerrormessages.blogspot.com...t-assigns.html

Happy Programming