SQL Function @return

[747576]

Hi there,
Below is a function which returns a value or name. The @return is declared as varchar, how do I change the sql to set the @return to FLOAT if its value. If the value is returned it has to be numeric.
Thanks



CREATE FUNCTION NEW_VALUE (@id VARCHAR(30)
, @field CHAR(1))
RETURNS VARCHAR(30)
AS

BEGIN
DECLARE @value VARCHAR(30)
DECLARE @vname VARCHAR(30)
DECLARE @return VARCHAR(30)

SELECT @vname = T1.VNAME, @value = CONVERT(VARCHAR,T1.VALUE)
FROM
(SELECT T.ID
,T.NAME VFNAME
,CASE WHEN T.VALUE < 0 THEN T.VALUE * -0.5 ELSE T.VALUE END VALUE
FROM TABLE T
) T1
IF @field = 'N'
SET @return = @value
ELSE
SET @return = @vname

RETURN @return
END
GO
/*******************************************************************************
Usage: SELECT dbo.NEW_VALUE('123','N')
SELECT dbo.NEW_VALUE('123','Y')
********************************************************************************/

 

[r937]

you will probably have better luck getting an answer in the Microsoft SQL Server forum (or is this perhaps Sybase?)

this is the ANSI SQL forum

FYI: the subquery T1 does not have a column called VNAME

r937.com | rudy.ca

 

[747576]

ok Thanks.