April 24, 2008 at 1:59 am
Hi,
I haven't been working with SQL for quite some time now but I'm back into the thick of things... :w00t:
I have a query (for now, will be a strored proc later) that calls a stored proc. The second sp uses the variables from sp1 to get data from a table and inserts it into a globaal temp table.
With every insert, on two fields, the following error pops up:
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'D229'.
Server: Msg 207, Level 16, State 1, Line 2
Invalid column name 'E01'.
I need to get this sorted otherwise my you know what will be on the line. Please see the scripts below. Any help will be highly appreciated.
Query
DECLARE
@PeriodEndDate DATETIME,
@EmployeeCode NCHAR(10),
@CompanyID NCHAR(3),
@CostCentreCode NCHAR(4),
@FiscalYear NCHAR(4),
@Period NCHAR(2),
@RecordCount BIGINT,
@LoopCount BIGINT,
@ValueNo INT
BEGIN
CREATE TABLE
##TMP
(
EmployeeCode NCHAR(10),
CompanyID NCHAR(3),
CostCentreCode NCHAR(4),
FiscalYear INT,
ComponentCode NCHAR(3),
Period INT,
nValue NUMERIC (18, 2)
)
SET @PeriodEndDate = (SELECT TOP 1 PeriodEndDate FROM CO_INFO_FIXED)
SET @FiscalYear = CONVERT(NCHAR(4), YEAR(@PeriodEndDate))
SET @Period = CONVERT(NCHAR(2), MONTH(@PeriodEndDate))
DECLARE
@DAT TABLE
(
RecordCount BIGINT IDENTITY (1, 1),
EmployeeCode NCHAR(10),
CompanyID NCHAR(3),
CostCentreCode NCHAR(4)
)
INSERT INTO
@DAT
(
EmployeeCode,
CompanyID,
CostCentreCode
)
SELECT
EmployeeCode,
CoNo,
CategoryCode
FROM
EMP_INFO_FIXED INNER JOIN
EMP_CO_CONTRIB ON
EMP_INFO_FIXED.PayPeriod = EMP_CO_CONTRIB.PayPeriod AND
EMP_INFO_FIXED.CoNo = EMP_CO_CONTRIB.CoNo AND
EMP_INFO_FIXED.EmployeeCode = EMP_CO_CONTRIB.EmployeeCode INNER JOIN
EMP_TOTALS ON
EMP_INFO_FIXED.PayPeriod = EMP_TOTALS.PayPeriod AND
EMP_INFO_FIXED.CoNo = EMP_TOTALS.CoNo AND
EMP_INFO_FIXED.EmployeeCode = EMP_TOTALS.EmployeeCode
WHERE
EMP_INFO_FIXED.PayPeriod = 'C' AND
(EMP_TOTALS.EarnTotal_Current <> 0.00 OR
EMP_TOTALS.NettPay_Current <> 0.00 OR
EMP_CO_CONTRIB.CC_Total <> 0.00)
SET @RecordCount = 1
SET @LoopCount = (SELECT COUNT(EmployeeCode) FROM @DAT)
WHILE @RecordCount <= @LoopCount
BEGIN
SET @ValueNo = 1
SET @EmployeeCode = (SELECT EmployeeCode FROM @DAT WHERE RecordCount = @RecordCount)
SET @CompanyID = (SELECT CompanyID FROM @DAT WHERE RecordCount = @RecordCount)
SET @CostCentreCode = (SELECT CostCentreCode FROM @DAT WHERE RecordCount = @RecordCount)
WHILE @ValueNo <= 72
BEGIN
EXEC get_IduData @ValueNo, @EmployeeCode, @CompanyID, @CostCentreCode, @FiscalYear, @Period
SET @ValueNo = @ValueNo + 1
END
SET @RecordCount = @RecordCount + 1
END
DROP TABLE ##TMP
END
Stored Procedure
ALTER PROCEDURE
dbo.[get_IduData]
@ValueNo INT,
@EmployeeCode NCHAR(10),
@CompanyID NCHAR(3),
@CostCentreCode NCHAR(4),
@FiscalYear INT,
@Period INT
AS
DECLARE
@GetEarnings NVARCHAR(4000),
@GetCompanyContributions NVARCHAR(4000),
@GetNettSalary NVARCHAR(4000),
@ComponentCode NCHAR(3),
@ComponentValue NCHAR(2),
@Type NVARCHAR(56),
@ValueNoStr AS NCHAR(2)
BEGIN
SET @ComponentCode = 'E' + CASE WHEN @ValueNo < 10 THEN '0' + CAST(@ValueNo AS NCHAR(1)) ELSE CAST(@ValueNo AS NCHAR(2)) END
IF @ValueNo = 1
BEGIN
SET @Type = 'EarnFixed_'
END
ELSE
BEGIN
SET @Type = 'Earnings_'
END
SET @ValueNoStr = CASE WHEN @ValueNo < 10 THEN '0' + CAST(@ValueNo AS NCHAR(1)) ELSE CAST(@ValueNo AS NCHAR(2)) END
SET @GetEarnings = N'
INSERT INTO
##TMP
(
EmployeeCode,
CompanyID,
CostCentreCode,
FiscalYear,
ComponentCode,
Period,
nValue
)
SELECT
' + @EmployeeCode + ',
' + @CompanyID + ',
' + @CostCentreCode + ',
CONVERT(INT, ' + CONVERT(NCHAR(4), @FiscalYear) + ')' + ',
' + @ComponentCode + ',
CONVERT(INT, ' + CONVERT(NCHAR(2), @Period) + ')' + ',
' + @Type + @ValueNoStr + '
FROM
EMP_EARNINGS
WHERE
PayPeriod = ''C'' AND
EmployeeCode = ' + @EmployeeCode
EXEC sp_executesql @GetEarnings
END
April 24, 2008 at 2:11 am
If the values aren't in quotes, SQL interprets then as columns.
Try something like this (partial query only)
INSERT INTO ....
SELECT ''' + @EmployeeCode + ''', ....
Easiest way to find and fix these kinds of problems with dynamic SQL is to PRINT the setring first. Then you can look over it and you'd probably eb able to spot the errors
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
April 24, 2008 at 2:20 am
Thank you GM... I'll give it a shot and let you know...
Later...
S
April 24, 2008 at 5:23 am
Simple, yet effective 😉 Thank you GM... You're a star!
Later...
S
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply