TSQL - Count specific values within Multistatement table UDF and pass them to additional column

during coding my project I have encountered an obstacle and cannot go through with this issue...

here is what I would like to achieve, I have a simple table which stores data connected with football players like: Number (ID), Name, Goals (are additional ones, but right now are irrelevant) and I have created a UDF Multistatement table LotOfGoals which looks as follows:

CREATE FUNCTION LotOfGoals()
    RETURNS @Players TABLE
    (
        Number INT,
        Name VARCHAR(20),
        Goals INT
        FuzzyLevel FLOAT(3) --extra column which I would like to add to result
    )
AS
BEGIN
    INSERT    @Players
    SELECT   Number, Name, Goals
    FROM     FuzzyFootballTeam
    WHERE    Goals > 2 
    ORDER BY Number
    -- here FuzzyLevel column should include data counted by MembershipLevel 
    -- scalar UDF.
    -- I want to pass each number of goals into MembershipLevel function and     
    -- insert return value into a new column FuzzyLevel.         
RETURN
    END
    GO

now MembershipLevel function:

CREATE FUNCTION MembershipLevel(@Goals INT)
RETURNS float(3)
AS
BEGIN
    DECLARE @Level float(3)

    SET @Level = 0.25*@Goals - 0.5;

    RETURN @Level
END

As I have written, after WHERE clause I would like to pass each number of goals to a MembershipLevel and then its return value insert into new column FuzzyLevel.

I would be really really grateful for any hint, idea etc. Thanks in advance !

True, I will change to in-line one. One more question is there any way to use FuzzyLevel column in where clause ? (I receive Invalid column name 'FuzzinessLevel') what I want is to limit allowed fuzzylevel. I have expanded both functions with one more additional argument @AcceptedFuzzyLevel float and the scalar function looks like this:

DECLARE @Level float(3)开发者_如何学JAVA
DECLARE @TempLevel float(3)

IF (@Goals <= 2)
    SET @TempLevel = 0;
    IF (@TempLevel >= @FuzzyLevelAccepted)
    SET @Level = @TempLevel;

ELSE IF (@Goals > 2 AND @Goals < 6)
SET @TempLevel = 0.25*@Goals - 0.5;
IF (@TempLevel >= @FuzzyLevelAccepted)
SET @Level = @TempLevel;

    ELSE IF (@Goals >= 6)
    SET @TempLevel = 1;
    IF (@TempLevel >= @FuzzyLevelAccepted)
    SET @Level = @TempLevel;

RETURN @Level 

But after execution I also receive records with NULL values.

OK, I have fixed it. Just resolved following inequality: x > 4y + 2. Works but I`m curious why it is not possible to use new column in Where clause.

Thanks a million !

---

Just add it as a column because MembershipLevel is a scalar udf. It doesn't matter about the outer code (stored proc or tablek valued udf or SELECT)

INSERT    @Players (Number, Name, Goals, FuzzyLevel)
SELECT   Number, Name, Goals,

    dbo.MembershipLevel(Goals)

FROM     FuzzyFootballTeam ft
WHERE    Goals > 2 
ORDER BY Number

Observations: I'd explicitly specify the column list for @Players. I'd also ask myself why this isn't an in-line table valued function: a multi-statement udf is often a performance killer...