Return multiple values from query but subquery does not permitted.

Question

2.00/5 (2 votes)

See more:

i found solution to use top 1 in the query but it only return one top value. I know variable store only one value but i cant find any other way to do.
Here is my Stored Procedure
Please Help.

SQL

Table 1: dbo.tblEmpDetail (Name varchar(30), BasicPay int, ScaleID int)

SQL

Table 2: dbo.tblAllowance (Name varchar(30), Amount int, ScaleID int)

SQL

Table 3: dbo.tblDeduction (Name varchar(30), Amount int, ScaleID int)

SQL

  alter proc Test
as
begin
/*Tax cal variable*/
/*AllowanceDeduction cal vairable*/
declare @totaldeduc int,
 @totalAllowance int,
 @grosspay int
set @totaldeduc = ( select sum(Amount) from tblAllowance A
					   inner join tblEmpOfficialDetail O on O.ScaleID=A.ScaleID
					   group by A.ScaleID, O.EmpID) 
set @totalAllowance= ( select sum(Amount) from tblDeduction A
						  inner join tblEmpOfficialDetail O on O.ScaleID=A.ScaleID
						  group by A.ScaleID, O.EmpID)
set @grosspay= (select O.BasicPay from tblEmpOfficialDetail O 
					inner join tblAllowance A on A.ScaleID=O.ScaleID
					inner join tblDeduction D on D.ScaleID=O.ScaleID
					group by o.ScaleID,o.EmpID, O.BasicPay )+@totaldeduc-@totalAllowance
select @grosspay
end
Execute Test

What I have tried:

I am trying to calculate the gross pay of all employees by adding the allowances [dbo.tblAllowances] and subtracting the deductions [dbo.Deductions] against specific rank of employee from basic pay [dbo.EmpDetails] of the employee.

Posted 11-Jul-16 2:16am

Ribha Shakoor

Updated 4-Aug-16 0:26am

v3

Add a Solution

Comments

Suvendu Shekhar Giri 11-Jul-16 9:04am

The error is meaningful and it works that way for a reason. What exactly your requirement is?
Please expalin a little more about your requirement and we'll try to suggest an appropriate apprach.

Ribha Shakoor 11-Jul-16 11:03am

Check it now please

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

The Praveen Singh · Answer 1 · 2016-08-04T00:26:00

Use Temporary tables to achieve your goal some other method also available but this is easy..

SQL

Create Table #Temp
(
Sn                      INT IDENTITY,
EmpId                   int,
Name                    Varchar(150),
totaldeduc              decimal(10,2),
totalAllowance          decimal(10,2),
basicpay                decimal(10,2),
grosspay                decimal(10,2)
)
insert into #temp(EmpId)
select EmpId from tblEmpDetail

Update #temp set
totaldeduc=(select sum(Amount) from tblAllowance where EmpId=#temp.EmpId),
totalAllowance=(select sum(Amount) from tblDeduction where EmpId=#temp.EmpId),
basicpay=(select BasicPay from tblEmpOfficialDetail where EmpId=#temp.EmpId)

Update #Temp set grosspay=(basicpay+totalAllowance-totaldeduc)

Select * from #Temp
Drop table #Temp

I suppose in all your table EmpId is present.