How to give Parameter value to the function

  • Input parameter is EmpID, it will be inserted from C# application.
    I am  not  sure on how to pass the paramter value from C# side. The input will not be typed into the textbox in the current form. The value will be coming from another form.
    This is the function I am using now :

    USE [DB1]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:        <Author,,Name>
    -- Create date: <Create Date,,>
    -- Description:    <Description,,>
    -- =============================================
    alter FUNCTION [dbo].[fn_CalculateNetPay]
    (    
        @EmpID as int
    )
    RETURNS TABLE
    AS
    RETURN
    (
    select t.EmpID, t.BasePay*0.2 as TaxableIncome ,t.BasePay-t.BasePay*0.2 as NetPay
    --,PaySlipID autocrement
    from TestTable t
    inner join EmpTable e
    on t.EmpID= e.EmpID
    where t.EmpID=@EmpID

    )

  • 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.

  • Thank you for your reply and correcting my code. That parameter @EmpID I am trying to pass can't really passed into the function. I am having a gap I guess. My Application is always warning me "Must declare scalar value" for the function.

  • Newbi - Saturday, August 12, 2017 11:51 PM

    Thank you for your reply and correcting my code. That parameter @EmpID I am trying to pass can't really passed into the function. I am having a gap I guess. My Application is always warning me "Must declare scalar value" for the function.

    In your client application, I presume you're displaying information for one employee (in response to a click or some other event) or for a group of employees (for a page) at once.

    If you're showing a single employee, the example query above can be called from your .NET code.  If you're displaying a page, you should be able to use something similar to the following to include the data from the function.


    SELECT TOP 20 e.FirstName, e.LastName, p.TaxableIncome
      FROM dbo.EmpTable e
        CROSS APPLY dbo.fn_CalculateNetPay(e.EmpID) p
      WHERE whatever_your_using_to_return_a_page_of_rows_to_display
      ORDER BY e.LastName;

  • Thank you. I feel like I am connecting the dots. By the way, mind explaining me why you Select TOP 20 when using cross apply, rather than just select? Purely for the performance wise?

  • Neither. fn_CalculateNetPay is a function, and that's how you join a table to a table-valued function - by using APPLY

  • Newbi - Sunday, August 13, 2017 8:47 PM

    By the way, mind explaining me why you Select TOP 20 when using cross apply, rather than just select?

    Paging. So that the app gets back only 20 rows, not possibly thousands. If you have client-side paging, or if there's not much data, you won't need that.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster - Monday, August 14, 2017 1:43 AM

    Newbi - Sunday, August 13, 2017 8:47 PM

    By the way, mind explaining me why you Select TOP 20 when using cross apply, rather than just select?

    Paging. So that the app gets back only 20 rows, not possibly thousands. If you have client-side paging, or if there's not much data, you won't need that.

    Correct.  I only meant it as a placeholder for whatever mechanism you're using for paging.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply