SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to give Parameter value to the function


How to give Parameter value to the function

Author
Message
Newbi
Newbi
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2265 Visits: 680
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

)


Ed Wagner
Ed Wagner
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70264 Visits: 10984
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.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Newbi
Newbi
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2265 Visits: 680
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.
Ed Wagner
Ed Wagner
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70264 Visits: 10984
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;



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Newbi
Newbi
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2265 Visits: 680
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?
pietlinden
pietlinden
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18128 Visits: 14411
Neither. fn_CalculateNetPay is a function, and that's how you join a table to a table-valued function - by using APPLY
GilaMonster
GilaMonster
SSC Guru
SSC Guru (299K reputation)SSC Guru (299K reputation)SSC Guru (299K reputation)SSC Guru (299K reputation)SSC Guru (299K reputation)SSC Guru (299K reputation)SSC Guru (299K reputation)SSC Guru (299K reputation)

Group: General Forum Members
Points: 299383 Visits: 46697
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


Ed Wagner
Ed Wagner
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70264 Visits: 10984
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.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search