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 VNAME
,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')
********************************************************************************/

 

[vongrunt]

Always return varchar, do explicit conversion outside function, e.g:

select convert(float, dbo.NEW_VALUE('123', 'N'))

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]