Click here to Skip to main content
15,893,663 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
SQL
DECLARE	@return_value table(BonusSalary int,testid int)
insert	@return_value SELECT ( SUM(case when BonusSalary.Type=1 and BonusSalary.Active=1 then BonusSalary.BonusSalary else 0 end)-  SUM(case when BonusSalary.Type=0  and BonusSalary.Active=1 then BonusSalary.BonusSalary else 0 end)) AS Expr1, EmpID
FROM            BonusSalary
GROUP BY EmpID 

SELECT        Emp.ID, Emp.EmpName, Emp.NID, EmpEmail.Email, EmpAddress.Address, EmpPhone.Phone, EmpPhone.PhoneType, Salary.Salary, Salary.SalaryType, Positions.PositionName, MainCompany.Name,
                         WorkTimes.StartTime, WorkTimes.EndTime, Contracts.Contract, Contracts.Type, Emp_Contracts.ContractStartDate,@return_value.BonusSalary --, ContractStartEnd.Length, ContractStartEnd.Enddate, ContractStartEnd.Startdate
FROM            Emp left outer join EmpPhone on Emp.ID=EmpPhone.EmpID
					left outer join EmpEmail on Emp.ID=EmpEmail.EmpID
					left outer join EmpAddress on Emp.ID=EmpAddress.EmpID
					left outer join Emp_Salary ON Emp.ID = Emp_Salary.EmpID left outer join Salary ON Emp_Salary.SalaryID = Salary.ID
					left outer join Emp_Positions_Company ON Emp.ID = Emp_Positions_Company.EMPID left outer join MainCompany ON MainCompany.ID = Emp_Positions_Company.CompanyID left outer join Positions on Positions.ID=Emp_Positions_Company.PositionID
					left outer join WorkTimes on Emp.ID=WorkTimes.EmpID
                    left outer join Emp_Contracts on Emp.ID=Emp_Contracts.EmpID left outer join Contracts on Contracts.ID=Emp_Contracts.ContractID 
					LEFT OUTER JOIN @return_value on Emp.ID = @return_value.testid
--WHERE        (Salary.Active = 1) AND (EmpAddress.Active = 1) AND (EmpEmail.Active = 1) AND (Contracts.Active = 1) AND (Emp_Contracts.Active = 1) AND (EmpPhone.Active = 1) AND (WorkTimes.Active = 1) AND 
--                         (Emp_Positions_Company.Active = 1) AND (MainCompany.Active = 1) AND (Positions.Active = 1) AND (Emp.Active = 1) AND (ContractStartEnd.Active = 1) AND (Emp_Salary.Active = 1) AND 
--                         (BonusSalary.Active = 1) OR
--                         (Salary.Active IS NULL) AND (EmpAddress.Active IS NULL) AND (EmpEmail.Active IS NULL) AND (Contracts.Active IS NULL) AND (Emp_Contracts.Active IS NULL) AND (EmpPhone.Active IS NULL) AND 
--                         (WorkTimes.Active IS NULL) AND (Emp_Positions_Company.Active IS NULL) AND (MainCompany.Active IS NULL) AND (Positions.Active IS NULL) AND (Emp.Active IS NULL) AND (ContractStartEnd.Active IS NULL)
--                          AND (Emp_Salary.Active IS NULL) AND (BonusSalary.Active IS NULL)



i got error message
Msg 137, Level 16, State 1, Line 14
Must declare the scalar variable "@return_value".
Posted
Updated 29-Jun-15 6:30am
v2
Comments
Mathi Mani 29-Jun-15 12:57pm    
The syntax for insert is INSERT INTO @return_value followed by the SELECT statement. Your insert query is missing INTO. Please add it and see if it works.
Member 7912784 29-Jun-15 13:02pm    
i add it and the same error

Create procedure syntax is not correctly followed. Syntax should start with CREATE PROCEDURE.
Try Create a Stored Procedure[^].
 
Share this answer
 
SQL
SELECT ... ,rv.BonusSalary,...
 ....
 LEFT OUTER JOIN @return_value as rv
    on Emp.ID = rv.testid



some one help me to fix it in stackoverflow so i share it
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900