August 12, 2017 at 9:21 am
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
)
August 12, 2017 at 2:32 pm
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.
August 12, 2017 at 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.
August 13, 2017 at 10:57 am
Newbi - Saturday, August 12, 2017 11:51 PMThank 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;
August 13, 2017 at 8:47 pm
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?
August 13, 2017 at 10:44 pm
Neither. fn_CalculateNetPay is a function, and that's how you join a table to a table-valued function - by using APPLY
August 14, 2017 at 1:43 am
Newbi - Sunday, August 13, 2017 8:47 PMBy 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
August 14, 2017 at 5:09 am
GilaMonster - Monday, August 14, 2017 1:43 AMNewbi - Sunday, August 13, 2017 8:47 PMBy 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