Home Forums SQL Server 7,2000 T-SQL How to give Parameter value to the function RE: How to give Parameter value to the function

  • You're really close.  It sounds like you're just missing a point of syntax.  Note that I removed the word AS is the parameter definition.

    ALTER FUNCTION dbo.fn_CalculateNetPay(@EmpID Integer) RETURNS TABLE
    AS
    RETURN (
      SELECT t.EmpID, t.BasePay * 0.2 AS TaxableIncom, t.BasePay-t . BasePay * 0.2 AS NetPay
        FROM dbo.TestTable t
          INNER JOIN dbo.EmpTable e ON t.EmpID = e.EmpID
        WHERE t.EmpID = @EmpID
    );
    go

    I don't know that you need the dbo.EmpTable join at all.  If the columns shown really are the only ones you need, then you can eliminate the INNER JOIN completely.

    Then, you can call the function by passing the value from your application.  The SQL syntax is:

    SELECT *
      FROM dbo.fn_CalculateNetPay(14);

    Hope this helps.