The multi-part identifier "L.Labor4" could not be bound. 1

[pcdaveh]

I have a sql query that works fine until I place it into a string variable and try to execute the string. First the Good Code is below:

INSERT INTO ESOTempTables.dbo.GLPayRunEmpsEarnings (CompanyNum, PRID, VoucherNum, VoidRunIndicator, ekPRV, ekPRL, EarnNum, EarnAmt, Labor4, EmpDept, mydept, HomeGL)
SELECT TOP (100) PERCENT dbo.PayRun.CompanyNum, PRV.PRID, PRV.VoucherNum, dbo.PayRun.VoidRunIndicator, PRV.EmpKey AS ekPRV,
PRE.EmpKey AS ekPRL, PRE.EarnNum, PRE.EarnAmt, L.Labor4, PRV.EmpDept,
CASE WHEN L.Labor4 = '' THEN PRV.EmpDept WHEN L.Labor4 IS NULL
THEN PRV.EmpDept ELSE L.Labor4
END AS mydept,
--remove hard coded company Number
(SELECT HomeLabor3 AS Expr2
FROM Payroll_10014.dbo.Emp_Main
WHERE (EmpKey = PRV.EmpKey)) AS HomeGL

FROM dbo.PayRunVoucher AS PRV INNER JOIN
dbo.PayRun ON PRV.PRID = dbo.PayRun.RunNum INNER JOIN
dbo.PayRunEarnings AS PRE ON PRV.PRID = PRE.RunNum AND PRV.VoucherNum = PRE.VCID AND PRV.EmpKey = PRE.EmpKey LEFT OUTER JOIN
dbo.PayRunLabor AS L ON PRV.PRID = L.RunNum AND PRV.VoucherNum = L.VCID AND PRV.EmpKey = L.EmpKey
WHERE (dbo.PayRun.VoidRunIndicator = 0) AND (PRV.PRID = 357) AND (PRE.EarnAmt <> 0) AND
((SELECT EarnAmt
FROM dbo.PayRunLabor
WHERE (RunNum = 357) AND (EmpKey = PRV.EmpKey) AND (EarnNum = PRE.EarnNum)) IS NULL)

UNION

SELECT TOP (100) PERCENT dbo.PayRun.CompanyNum, PRV.PRID, PRV.VoucherNum, dbo.PayRun.VoidRunIndicator, PRV.EmpKey AS ekPRV,
dbo.PayRunLabor.EmpKey AS ekPRL, dbo.PayRunLabor.EarnNum, dbo.PayRunLabor.EarnAmt, dbo.PayRunLabor.Labor4, PRV.EmpDept,
CASE WHEN dbo.PayRunLabor.Labor4 = '' THEN PRV.EmpDept
WHEN dbo.PayRunLabor.Labor4 IS NULL THEN PRV.EmpDept ELSE dbo.PayRunLabor.Labor4
END AS mydept,

--remove hard coded company Number
(SELECT HomeLabor3 AS Expr2
FROM Payroll_10014.dbo.Emp_Main
WHERE (EmpKey = PRV.EmpKey)) AS HomeGL

FROM dbo.PayRunVoucher AS PRV INNER JOIN
dbo.PayRun ON PRV.PRID = dbo.PayRun.RunNum LEFT OUTER JOIN
dbo.PayRunLabor ON PRV.PRID = dbo.PayRunLabor.RunNum AND PRV.VoucherNum = dbo.PayRunLabor.VCID AND
PRV.EmpKey = dbo.PayRunLabor.EmpKey
WHERE (dbo.PayRun.VoidRunIndicator = 0) AND (PRV.PRID = 357) AND (dbo.PayRunLabor.EarnAmt IS NOT NULL)
AND dbo.PayRunLabor.EarnAmt <> 0
-----------------------------------------------------------

The only difference now is that I'm placing this int a string variable and trying to execute the string:
Alter PROCEDURE zzzzzzzzzzzTest

As
Declare @StrSQL as Varchar (5000)
Set @StrSQL =
'INSERT INTO ESOTempTables.dbo.GLPayRunEmpsEarnings (CompanyNum, PRID, VoucherNum, VoidRunIndicator, ekPRV, ekPRL, EarnNum, EarnAmt, Labor4, EmpDept, mydept, HomeGL)
SELECT TOP (100) PERCENT dbo.PayRun.CompanyNum, PRV.PRID, PRV.VoucherNum, dbo.PayRun.VoidRunIndicator, PRV.EmpKey AS ekPRV,
PRE.EmpKey AS ekPRL, PRE.EarnNum, PRE.EarnAmt, L.Labor4, PRV.EmpDept,
CASE WHEN L.Labor4 = '' THEN PRV.EmpDept WHEN L.Labor4 IS NULL
THEN PRV.EmpDept ELSE L.Labor4
END AS mydept,
--remove hard coded company Number
(SELECT HomeLabor3 AS Expr2
FROM Payroll_10014.dbo.Emp_Main
WHERE (EmpKey = PRV.EmpKey)) AS HomeGL

FROM dbo.PayRunVoucher AS PRV INNER JOIN
dbo.PayRun ON PRV.PRID = dbo.PayRun.RunNum INNER JOIN
dbo.PayRunEarnings AS PRE ON PRV.PRID = PRE.RunNum AND PRV.VoucherNum = PRE.VCID AND PRV.EmpKey = PRE.EmpKey LEFT OUTER JOIN
dbo.PayRunLabor AS L ON PRV.PRID = L.RunNum AND PRV.VoucherNum = L.VCID AND PRV.EmpKey = L.EmpKey
WHERE (dbo.PayRun.VoidRunIndicator = 0) AND (PRV.PRID = 357) AND (PRE.EarnAmt <> 0) AND
((SELECT EarnAmt
FROM dbo.PayRunLabor
WHERE (RunNum = 357) AND (EmpKey = PRV.EmpKey) AND (EarnNum = PRE.EarnNum)) IS NULL)

UNION

SELECT TOP (100) PERCENT dbo.PayRun.CompanyNum, PRV.PRID, PRV.VoucherNum, dbo.PayRun.VoidRunIndicator, PRV.EmpKey AS ekPRV,
dbo.PayRunLabor.EmpKey AS ekPRL, dbo.PayRunLabor.EarnNum, dbo.PayRunLabor.EarnAmt, dbo.PayRunLabor.Labor4, PRV.EmpDept,
CASE WHEN dbo.PayRunLabor.Labor4 = '' THEN PRV.EmpDept
WHEN dbo.PayRunLabor.Labor4 IS NULL THEN PRV.EmpDept ELSE dbo.PayRunLabor.Labor4
END AS mydept,

--remove hard coded company Number
(SELECT HomeLabor3 AS Expr2
FROM Payroll_10014.dbo.Emp_Main
WHERE (EmpKey = PRV.EmpKey)) AS HomeGL

FROM dbo.PayRunVoucher AS PRV INNER JOIN
dbo.PayRun ON PRV.PRID = dbo.PayRun.RunNum LEFT OUTER JOIN
dbo.PayRunLabor ON PRV.PRID = dbo.PayRunLabor.RunNum AND PRV.VoucherNum = dbo.PayRunLabor.VCID AND
PRV.EmpKey = dbo.PayRunLabor.EmpKey
WHERE (dbo.PayRun.VoidRunIndicator = 0) AND (PRV.PRID = 357) AND (dbo.PayRunLabor.EarnAmt IS NOT NULL)
AND dbo.PayRunLabor.EarnAmt <> 0'

Execute (@StrSQL)

----------------------------------------------------
Error messsages below:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "PRE.EmpKey" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "PRE.EarnNum" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "PRE.EarnAmt" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "L.Labor4" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "L.Labor4" could not be bound.

 

[SQLSister]

In the first place why on earth would you want to run that as a string variable? You aren't changing anything dynamically so you gain nothing by putting it into a string.

If you insist on using an unnecessary method, then I suggest you print the @StrSQL variable and see what the value is. You will probaly find the error then. Also if you are going to put stuff this long into a variable you need to start being in the habit of removing unnecessary items like top 100 percent and unneeded parentheses because sooner or later you will run out of room in the variable. Also subselects are usually slower than joins or derived tables. You should get out of the habit of using them as well.

And please when you post something this long take the time to format it so it is easier for us to read.


"NOTHING is more important in a database than integrity." ESquared

 

[pcdaveh]

Thanks for the suggestion of printing out the string. That yielded some results. I'm not a SQL Server expert, but an Accountant. So, some of the more sophisticated ways of doing things aren't going to be for me. Also, the reason this has to be put into a string variable is because other parts of this same query will be come dynamic. I resolving one issue at a time to eliminate having multiple variables.

 

[SQLSister]

I suggest you read these references to understand why dynamic sql is bad and some better methods of doing things.

http://www.unixwiz.net/techtips/sql-injection.html

http://www.sommarskog.se/dynamic_sql.html

"NOTHING is more important in a database than integrity." ESquared

 

[SQLSister]

As ana accountant I ask you to consider it from an accounting perspective.

Allowing dynamic sql is bad from an internal control perspective because you must allow users direct access to the tables and because you cannot assure that that you know what kind of inserts, updates, deletes the system will perform. I would personally would not allow dynamic sql anywhere in an accounting system. This is the kind of thing we would have pinged an organization on when I worked for an audit agency. This is a very significant vunerablity you are introducing to your critical system. Dynamic SQl makes it much easier for someone to commit fraud.

"NOTHING is more important in a database than integrity." ESquared