This is my first post and not sure how to fix this auto format draft? Using SQL Server 2000. The table variable that is giving me this one error is '@SBLH' The following is the multi-select function producing this error:
USE [OfficeData]
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[WC_Period_Wage_Class]
(
@StartPED datetime,
@EndPED datetime,
@StartPeriod datetime,
@EndPeriod datetime
)
RETURNS
@WcWages TABLE (
WorkDate datetime,
PED datetime,
Amount5213 money,
Amount5221 money,
Amount8810 money,
AmountOther money,
Reason nvarchar(50),
Reportable int
)
AS
BEGIN
DECLARE @SBLH TABLE (
Project nvarchar(10),
EmplID int,
Phase nvarchar(5),
Cost nvarchar(5),
Type nvarchar(2),
Amount money,
PayED datetime,
WkDate datetime,
RunTotal money
)
INSERT INTO @SBLH (
Project,
EmplID,
Phase,
Cost,
Type,
Amount,
PayED,
WkDate
)
SELECT job,
emp,
phs,
cst,
ty,
Sum(glamt) AS Amt,
payend_dt,
CASE
WHEN Cast(day1 AS int) > 0 THEN DATEADD(d, -6, payend_dt)
WHEN Cast(day2 AS int) > 0 THEN DATEADD(d, -5, payend_dt)
WHEN Cast(day3 AS int) > 0 THEN DATEADD(d, -4, payend_dt)
WHEN Cast(day4 AS int) > 0 THEN DATEADD(d, -3, payend_dt)
WHEN Cast(day5 AS int) > 0 THEN DATEADD(d, -2, payend_dt)
WHEN Cast(day6 AS int) > 0 THEN DATEADD(d, -1, payend_dt)
WHEN Cast(day7 AS int) > 0 THEN payend_dt
ELSE DATEADD(d, -2, payend_dt)
END AS WDate
FROM SB_PRLaborHist
WHERE (co = '1') AND
(glamt > 0)
GROUP BY job,
emp,
phs,
cst,
ty,
payend_dt,
CASE
WHEN Cast(day1 AS int) > 0 THEN DATEADD(d, -6, payend_dt)
WHEN Cast(day2 AS int) > 0 THEN DATEADD(d, -5, payend_dt)
WHEN Cast(day3 AS int) > 0 THEN DATEADD(d, -4, payend_dt)
WHEN Cast(day4 AS int) > 0 THEN DATEADD(d, -3, payend_dt)
WHEN Cast(day5 AS int) > 0 THEN DATEADD(d, -2, payend_dt)
WHEN Cast(day6 AS int) > 0 THEN DATEADD(d, -1, payend_dt)
WHEN Cast(day7 AS int) > 0 THEN payend_dt
ELSE DATEADD(d, -2, payend_dt)
END
HAVING (payend_dt Between @StartPED And @EndPED)
ORDER BY emp,
WDate
-- Update the temporary table #EBLH with a running total reset for each employee
DECLARE @RunTotal money,
@EID int
SET @RunTotal = 0
SET @EID = 0
UPDATE @SBLH
SET @RunTotal = RunTotal = CASE
WHEN EmplID = @EID
THEN @RunTotal + Amount
ELSE Amount
END,
@EID = EmplID
-- Remove wages above the $36,000 cap and compile into the three WC classes
-- Error is caused by the following code:
INSERT INTO @WcWages (
WorkDate,
PED,
Amount5221,
Amount5213,
Amount8810,
AmountOther,
Reason,
Reportable
)
SELECT WkDate,
PayED,
Sum(CASE
WHEN JC.JC_WC_Class_Code = '5221'
THEN Amount
ELSE 0
END) AS Amt5221,
Sum(CASE
WHEN JC.JC_WC_Class_Code = '5213'
THEN Amount
ELSE 0
END) AS Amt5213,
Sum(CASE
WHEN JC.JC_WC_Class_Code = '8810'
THEN Amount
ELSE 0
END) AS Amt8810,
Sum(CASE
WHEN Len(JC.JC_WC_Class_Code) = 0
THEN Amount
ELSE 0
END) AS AmtOther,
CASE
WHEN Len(JC.JC_WC_Class_Code) > 0
THEN ''
ELSE JC.JC_Phase_Descrip + '-' + JC.JC_Cost_Descrip
END AS OtherReason,
CASE
WHEN RunTotal > 36000.00
THEN 0
ELSE 1
END AS IsReportable
FROM @SBLH
LEFT JOIN JC_D_CodeMaster AS JC
ON @SBLH.Type = JC.JC_Cost_Type
AND @SBLH.Cost = JC.JC_Cost_No
AND @SBLH.Phase = JC.JC_Phase_No
AND @SBLH.Project = JC.Project_Number
LEFT JOIN JC_D_List_WC_Codes AS WC
ON JC.JC_WC_Class_Code = WC.JC_WC_Class_Code
GROUP BY @SBLH.WkDate,
@SBLH.PayED,
CASE
WHEN Len(JC.JC_WC_Class_Code) > 0
THEN ''
ELSE JC.JC_Phase_Descrip + '-' + JC.JC_Cost_Descrip
END,
CASE
WHEN RunTotal > 36000.00
THEN 0
ELSE 1
END
HAVING (@SBLH.WkDate Between @StartPeriod And @EndPeriod)
RETURN
END
GO
The error is the Insert code after the Update code. This should be one batch and the variable @SBLH should not be out of scope. I found on the internet references to the variable losing it's scope after an update. However, I have seen MS knowledge base examples showing a table variable used in a select statement after an update statement within a multi select function similar to my code above. I know it's something simple, but it is eluding me. This is the first multi select function I've written, so be patient with me.
Thanks for all the help.
