[RESOLVED] Sql Server 2000 Function, set variable to column value

I've got a table that holds a rate value and an effective date:

Code:

---

ID | EffectiveDate | AdminCost
1  | 2000-01-01    | 200.00
2  | 2010-01-01    | 225.00
3  | 2012-01-01    | 250.00

---

I'm trying to make a Function that will return the value of AdminCost column for a single record, I'm able to get that record with this query just fine:

Code:

---

Select Top 1 AdminCost, max(ID)
from tbl_pwe_tcr_DefaultAmounts
where EffectiveDate < @EntryDate
Group By ID, AdminCost

---

Which means when the date passed in is between "Jan 1 2010" and "Jan 1 2012" it returns the '225.00' and I'm trying to set that value to a function variable to return it to the Stored Proc that calls this, but when I try set it to a variable, I get an error, here's the function I'm trying to create:

Code:

---

Create Function [dbo].[fn_GetAdminFee] (@EntryDate DateTime)
Returns numeric(6, 2)
As
Begin
        Declare @Amount numeric(6, 2)

        Select Top 1 @Amount = AdminCost, max(ID)
        from tbl_pwe_tcr_DefaultAmounts
        where EffectiveDate < @EntryDate
        Group By ID, AdminCost

        Return @Amount
End

---

The error I'm getting is:

Code:

---

A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

---

So my question is, how can I assign the the AdminCost column to the @Amount variable?

I've also tried:

Code:

---

Declare @Amount numeric(6, 2)

Select @Amount = AdminCost From (Select AdminCost, max(ID)
    from tbl_pwe_tcr_DefaultAmounts
    where EffectiveDate < @EntryDate
    Group By ID, AdminCost)

---

but that doesn't work either.

Your SELECT statement must either just set the values of variables or else just return columns. It can't do both. You'll need to use a subquery. The inner query will get the two columns for the appropriate record and then the outer query can get just the one value from that and assign it to the variable.

Code:

---

SELECT @Amount = AdminCost FROM
(
        Select Top 1 AdminCost, max(ID)
        from tbl_pwe_tcr_DefaultAmounts
        where EffectiveDate < @EntryDate
        Group By ID, AdminCost
) A

---

I started to ask "But isn't that what he did in the last code example?" .... then I saw the difference, and it's something I miss myself from time to time... the sub query has to be aliased...

-tg

Ah, I didn't read that last code snippet in the first post properly so that was almost correct but not quite.

Thanks guys, I was forgetting to give it an alias.