August 18, 2017 at 12:27 am
I am getting this error:subquery returned more than 1 value, this is not permitted when the subquery follow........whenever I try to run this function.
USE [DB1]GO SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER FUNCTION fn_PayrollConfirm (@EmpID int)RETURNS @PayrollConfirm TABLE (PayslipID int identity(1,1), EmpID int, TaxableIncome int, NetPay int) ASBEGIN DECLARE @TotalDays int; SET @TotalDays = (select datediff(day,StartDate,EndDate) from WorkingRecord); IF (@TotalDays =30 or @TotalDays <=31) BEGIN INSERT INTO @PayrollConfirm SELECT EmpID, BasePay * 0.2 AS TaxableIncome, BasePay-BasePay * 0.2 AS NetPay FROM dbo.WorkingPeriod WHERE EmpID = @EmpID END; ELSE IF (@TotalDays <30) BEGIN INSERT INTO @PayrollConfirm SELECT EmpID,BasePay * 0.2 AS TaxableIncome,BasePay/30*@TotalDays as NetPay FROM dbo.WorkingPeriod WHERE EmpID = @EmpID END; RETURN;END--select * from fn_PayrollConfirm(11)
In that function,I am trying to calculate TaxableIncome and NetPay according to @TotalDays(a parameter which counts number of working day). And put those values to a table called PayrollConfirm( where I have added PayslipID which is auto generated )
My expected result from function is only one row displaying PayslipID,EmpID,TaxableIncome,NetPay.
August 18, 2017 at 1:15 am
Formatted so that it's actually readable.
ALTER FUNCTION fn_PayrollConfirm (@EmpID INT)
RETURNS @PayrollConfirm TABLE (
PayslipID INT IDENTITY(1, 1),
EmpID INT,
TaxableIncome INT,
NetPay INT
)
AS
BEGIN
DECLARE @TotalDays INT;
SET @TotalDays = (SELECT DATEDIFF (DAY, StartDate, EndDate) FROM WorkingRecord);
IF (@TotalDays = 30
OR @TotalDays <= 31)
BEGIN
INSERT INTO @PayrollConfirm
SELECT EmpID,
BasePay * 0.2 AS TaxableIncome,
BasePay - BasePay * 0.2 AS NetPay
FROM dbo.WorkingPeriod
WHERE EmpID = @EmpID;
END;
ELSE
IF (@TotalDays < 30)
BEGIN
INSERT INTO @PayrollConfirm
SELECT EmpID,
BasePay * 0.2 AS TaxableIncome,
BasePay / 30 * @TotalDays AS NetPay
FROM dbo.WorkingPeriod
WHERE EmpID = @EmpID;
END;
RETURN;
END
Most likely this returns more than one row:
SELECT DATEDIFF (DAY, StartDate, EndDate) FROM WorkingRecord
Since you're using it with SET, it must return one and only one row.
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 18, 2017 at 1:44 am
Thank you. How could I format nicely, especially for block of sql code for better readability? I've edited select datediff(day,wr.StartDate,wr.EndDate) from WorkingRecord wr inner join EmpTbl e ON wr.EmpID=e.EmpID where e.EmpID=@EmpID and the function is get me one row result. But will It actually store in PayConfirmTbl? For example if I want to use that PayConfirmTbl alone in another place?
August 18, 2017 at 1:51 am
Newbi - Friday, August 18, 2017 1:44 AMBut will It actually store in PayConfirmTbl? For example if I want to use that PayConfirmTbl alone in another place?
Huh?
To store something in that table, insert it, or update an existing row.
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 18, 2017 at 3:01 am
I know why because that @PayrollConfirmTable is a temporary one and thus i can't get result when I execute the table outside of the function. How can I make it as a separate table and recall from some where else of the application.
August 18, 2017 at 3:38 am
Not related to the question, but to the SQL provided.
Why is the first IF statement IF (@TotalDays = 30 OR @TotalDays <= 31) and the ELSE IF statement ELSE IF (@TotalDays < 30). The second statement will never be fulfilled, as if @TotalDays less than 30, then it's less than or equal for 31 (basic maths). As the first IF statement's prerequisite is fulfilled, no other parts of the IF (ELSE) will be entered.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 18, 2017 at 3:53 am
Newbi - Friday, August 18, 2017 3:01 AMI know why because that @PayrollConfirmTable is a temporary one and thus i can't get result when I execute the table outside of the function. How can I make it as a separate table and recall from some where else of the application.
There's nothing in @PayrollConfirmTable because your function errors before it gets to inserting into it. Once you've sorted that out, you can put the results into a temporary or permanent table so that you can query them:SELECT *
INTO #PayrollConfirmTable
FROM fn_PayrollConfirm;
John
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply