How do I Create table in procedure and use in in select with join?

Question

DECLARE @return_value table(BonusSalary int, testid int)

INSERT INTO @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

i got error message Msg 137, Level 16, State 1, Line 14 Must declare the scalar variable "@return_value".

It might be because you need the word "into" after the word "insert". Or it could be that you need the word "as" between the words "@return_value" and "table". — Dan Bracuk, Jun 29 '15 at 16:38
That parses fine for me, it that the *exact* statement? - (INTO/AS are optional) — Alex K., Jun 29 '15 at 16:51
Yes I've taken a closer look too (now) and the code seems to be ok. Only idea left is that he doesn't run it as a whole transaction just step by step. This will deallocate the table variable after he inserted it. — Ionic, Jun 29 '15 at 16:54
in the join condition you are using testid which isn't in the @return_value table. — Vamsi Prabhala, Jun 29 '15 at 17:09
vkp, yes it is... Look at the table declaration. The column names in the insert statement don't matter. — Wolves, Jun 29 '15 at 17:12

score 1 · Accepted Answer · answered Jun 29 '15 at 17:17

1

You cannot join table variables without using an alias. Try this:

 SELECT ... ,rv.BonusSalary,...
 ....
 LEFT OUTER JOIN @return_value as rv 
    on Emp.ID = rv.testid

answered Jun 29 '15 at 17:17

Wolves

515
3
7
15

thx alot it fix the error – Black Ghost Jun 29 '15 at 17:32
Not only is this the fix for the OP hopefully it will also server as a lesson that they should always use aliases. It makes everything so much easier. – Sean Lange Jun 29 '15 at 19:39

How do I Create table in procedure and use in in select with join?

1 Answers1