August 10, 2011 at 7:05 am
Sir,
I have written a function but giving above error how to solve the problem please show me.
[Code]
CREATE FUNCTION dbo.uf_GetClg_Rate
(@Co As Varchar(02),
@Itemcd As Varchar(10),
@Date As Datetime)
RETURNS NUMERIC(14,4)
AS
BEGIN
--This Query Getting closing rate of last date within given period
--!
DECLARE @Retval AS NUMERIC(14,4)
SET @Retval=(SELECT CO,[DATE],CLNG_RATE FROM RATES WHERE ITEMCD=@Itemcd AND
[DATE]=(SELECT MAX([DATE]) FROM RATES WHERE [DATE]<=@Date AND CLNG_RATE<>0))
--!
Return (@Retval)
END
[/code]
August 10, 2011 at 7:11 am
What do you want to set @Retval to - CO, [DATE] or CLNG_RATE?
John
August 11, 2011 at 8:55 am
what you probably want is all three i'm guessing. so you could do something like this:
CREATE FUNCTION dbo.uf_GetClg_Rate
(@Co As Varchar(02),
@Itemcd As Varchar(10),
@Date As Datetime)
RETURNS NUMERIC(14,4)
AS
BEGIN
--This Query Getting closing rate of last date within given period
--!
DECLARE @Retval AS NUMERIC(14,4)
SET @Retval=(SELECT 'CO: ' + CO + ' - DATE: ' + [DATE] + ' - CLNG_RATE' + CLNG_RATE FROM RATES WHERE ITEMCD=@Itemcd AND
[DATE]=(SELECT MAX([DATE]) FROM RATES WHERE [DATE]<=@Date AND CLNG_RATE<>0))
--!
Return (@Retval)
END
If you actually want to return all three, then what you want to do is use a table-valued function.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply