Is there a way to view temporary local tables? 1

[LMGroup]

I've modified some code that was supplied by the software vendor but I'm not getting the results I'd hoped for. I'm using SQL Server 2005 and Management Studio. I modified the code so that records containing "Helper" in the CCName would be totalled separately from the other records (or so I thought!). I'm only getting zeroes for those new totals and the original totals still contain all the records. Is there a way to see the temporary tables so that I can check if my table modifications worked?

The original code (from the vendor) is contained in my post from 2 days ago. (I didn't think you'd want it posted here again) This is the modified code that is supposed to calculate the Helper Costs and Hours separately from the other totals.

Thanks!

/****** Object:  StoredProcedure [dbo].[csp_labRepDept]    Script Date: 05/23/2012 09:06:59 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--/*          ***** Source Level:1.16 *****/
--$Revision: 1.16 $
/****** Object:  Stored Procedure dbo.labRepDept

  -----------------------------------------------------------------------
  Returns data for Labor Report - Department Detail and Summary
	-----------------------------------------------------------------------
	
	5/23/12 - GTK generated copy of standard SP code for Lowe-Martin
	5/24/12 - LLM modified the code to remove Helper LCCs from the Cost Centres and total them separately
*/

ALTER proc [dbo].[csp_LMlabRepDept]
	@ReportType integer,
	@SelLine varchar(6000),
	@ShiftSort integer,
	@YTDStart datetime,
	@QTDStart datetime,
	@PeriodStart datetime,
	@PeriodEnd datetime,
	@RepDateSel integer

AS
BEGIN

SET NOCOUNT ON
	CREATE TABLE #ProdData 
		(LabCCN int, LabShift int, Hrs decimal (9,2), Cost decimal(15,2), Qty decimal(15,2), IsYTD int, IsQTD int, IsCur int)

	CREATE TABLE #ProdSum (LabCCN int, LCCDesc varchar(40) COLLATE database_default, DeptNum int, 
					CCNum int, Standard decimal(9,2), AIC decimal(15,2), LabShift int, 
					TotHrs decimal(15,2), TotCost decimal(15,2), TotQty decimal(15,2), IsYTD int, IsQTD int, IsCur int, HlpHrs decimal(15,2), HlpCost decimal(15,2))


declare @EmpDet tinyint, @EmpSum tinyint, @DeptDet tinyint, @DeptSum tinyint
SET @EmpDet = 1
SET @EmpSum = 2
SET @DeptDet = 3
SET @DeptSum = 4
declare @DeptBrk int
SELECT @DeptBrk = dbo.zlaGetNParm(10160)
IF @DeptBrk = 0 SET @DeptBrk = 10000	--Don't allow a zero value

declare @SQLLine varchar(7000)

declare @BeginDate datetime
declare @ZeroDate as datetime  Set @ZeroDate = '1900-01-01'
declare @NtFnd as char(17)  Set @NtFnd = '<<< Not Found >>>'
declare @ReWorkCC as int  Set @ReWorkCC = 70
declare @NonChargeCC as int  Set @NonChargeCC = 71

SET @RepDateSel = 1  --Cur
SET @BeginDate = @PeriodStart

-- Set them to inoperative dates, if not used
if @QTDStart = @ZeroDate
	SET @QTDStart = @PeriodStart
if @YTDStart = @ZeroDate 
	SET @YTDStart = @QTDStart

		--Pick up Department names
		declare @DeptTable table (DeptN int, DeptName varchar(40) COLLATE database_default)
		insert @DeptTable 
			SELECT LCCN, LCCDescription FROM dbo.LaborCostCntr WHERE (LCCN/@DeptBrk)* @DeptBrk = LCCN

		--Pick up Cost Center names
		declare @CCTable table (CCN int, CCName varchar(40) COLLATE database_default, CCStd decimal(15,2), CCRate decimal(9,2))
		insert @CCTable 
			SELECT LCCN, LCCDescription, Standard, AICCostRate FROM dbo.LaborCostCntr WHERE (LCCN/100)* 100 = LCCN

if @ReportType = @DeptSum or @ReportType = @DeptDet 
begin
	
	--Collect Labor
	
	SET @SQLLine = 'INSERT INTO #ProdData SELECT '
	SET @SQLLine = @SQLLine + ' CASE WHEN Prefix in (2,5,8) THEN floor(LCCN/100)*100 + ' + cast(@ReworkCC as char(2)) + ' ELSE LCCN END,' 
	if @ShiftSort = 1 
		SET @SQLLine = @SQLLine + 'Shift,'
	else
		SET @SQLLine = @SQLLine + '0,'
	
	SET @SQLLine = @SQLLine + 'Hours, AICCost, LaborQuantity, '	
	SET @SQLLine = @SQLLine + 'CASE WHEN UpdateDate >= ''' + cast(@YTDStart as varchar(20)) + ''' and UpdateDate <= ''' + cast(@PeriodEnd as varchar(20)) + ''' THEN 1 ELSE 0 END,'
	SET @SQLLine = @SQLLine + 'CASE WHEN UpdateDate >=''' + cast(@QTDStart as varchar(20)) + ''' and UpdateDate <= ''' + cast(@PeriodEnd as varchar(20)) + ''' THEN 1 ELSE 0 END,'
	SET @SQLLine = @SQLLine + 'CASE WHEN UpdateDate >=''' + cast(@PeriodStart as varchar(20)) + ''' and UpdateDate <=''' + cast(@PeriodEnd as varchar(20)) + ''' THEN 1 ELSE 0 END'
	SET @SQLLine = @SQLLine + ' FROM dbo.JobLabor '
	SET @SQLLine = @SQLLine + ' WHERE UpdateDate >=''' + cast(@BeginDate as varchar(20)) + ''' AND UpdateDate <=''' + cast(@PeriodEnd as varchar(20)) + ''''
	SET @SQLLine = @SQLLine + ' AND LCCN > 2 '
	
		if len(@SelLine) > 0 
			SET @SQLLine = @SQLLine + ' AND ' + @SelLine
	
		EXEC (@SQLLine)

	--Now return the report data
			--Summarize data by LCC, etc.
			INSERT #ProdSum
			SELECT LabCCN, isnull(LCCDescription, CASE LabCCN %100 WHEN @ReworkCC THEN 'Rework' 
							WHEN @NonChargeCC THEN 'Non Chargeable' ELSE @NtFnd END) LCCDesc, 
							dbo.lccDept(LabCCN, @DeptBrk), dbo.lccCostCntr(LabCCN), isnull(Standard,0), isnull(AICCostRate,0), LabShift, 
							isnull(Sum(Hrs),0) TotHrs, isnull(Sum(Cost),0) TotCost, isnull(Sum(Qty),0) TotQty, IsYTD, IsQTD, IsCur, isnull(Sum(Hrs),0) HlpHrs, isnull(Sum(Cost),0) HlpCost
				FROM #ProdData LEFT OUTER JOIN dbo.LaborCostCntr ON #ProdData.LabCCN = LaborCostCntr.LCCN
				GROUP BY LabShift, LabCCN, LCCDescription, Standard, AICCostRate, IsYTD, IsQTD, IsCur

			SELECT LabShift, DeptNum, isnull(DeptName, @NtFnd) DeptName, CCNum,
				isnull(CCName, @NtFnd) CCName, isnull(CCStd,0) CCStd, isnull(CCRate, 0) CCRate,
				Sum(CASE WHEN CCName not like '%Helper%' 
					THEN TotHrs * (CASE WHEN (LabCCN % 100) >= @ReWorkCC THEN 0 ELSE 1 END)* IsCur
					ELSE 0 END) AS ChgHrs,
				Sum(CASE WHEN CCName LIKE '%Helper%' 
					THEN HlpHrs * (CASE WHEN (LabCCN % 100) >= @ReWorkCC THEN 0 ELSE 1 END)* IsCur
					ELSE 0 END) AS HelpHrs,
				Sum(TotHrs * IsCur) TotHrs,
				Sum(CASE WHEN CCName not like '%Helper%' 
					THEN TotCost * (CASE WHEN (LabCCN % 100) >= @ReWorkCC THEN 0 ELSE 1 END)* IsCur
					ELSE 0 END) AS Chg,
				Sum(CASE WHEN CCName LIKE '%Helper%' 
					THEN HlpCost * (CASE WHEN (LabCCN % 100) >= @ReWorkCC THEN 0 ELSE 1 END)* IsCur
					ELSE 0 END) AS HelpChg,
				Sum(TotHrs * (CASE WHEN (isnull(Standard,0) = 0) THEN 0 ELSE 1 END)* IsCur) HrsWStd,
				Sum(dbo.StdLabHrs(TotQty, isnull(Standard,0))* IsCur) StdLabHrs,
				Sum(TotHrs * (CASE WHEN (LabCCN % 100) >= @ReWorkCC THEN 0 ELSE 1 END)* IsQTD) QChgHrs,
				Sum(TotHrs * IsQTD) QTotHrs,
				Sum(TotCost * (CASE WHEN (LabCCN % 100) >= @ReWorkCC THEN 0 ELSE 1 END)* IsQTD) QChg,
				Sum(TotHrs * (CASE WHEN (isnull(Standard,0) = 0) THEN 0 ELSE 1 END)* IsQTD) QHrsWStd,
				Sum(dbo.StdLabHrs(TotQty, isnull(Standard,0))* IsQTD) QStdLabHrs,
				Sum(TotHrs * (CASE WHEN (LabCCN % 100) >= @ReWorkCC THEN 0 ELSE 1 END)* IsYTD) YChgHrs,
				Sum(TotHrs ) YTotHrs,
				Sum(TotCost * (CASE WHEN (LabCCN % 100) >= @ReWorkCC THEN 0 ELSE 1 END)* IsYTD) YChg,
				Sum(TotHrs * (CASE WHEN (isnull(Standard,0) = 0) THEN 0 ELSE 1 END)* IsYTD) YHrsWStd,
				Sum(dbo.StdLabHrs(TotQty, isnull(Standard,0))* IsYTD) YStdLabHrs

				FROM #ProdSum LEFT OUTER JOIN @CCTable ON CCNum = CCN
							LEFT OUTER JOIN @DeptTable ON DeptN = DeptNum
				GROUP BY LabShift, DeptNum, DeptName, CCNum, CCName, CCStd, CCRate
				order by labshift, deptnum, ccnum
end
END
GO

 

[IanWaterman]

You can place an End statement at any point in your Stored Proc, then just before that add
Select * from yourtemptablename

Compile Stored Proc and execute it should now just return dataset from temp table

Don't forget to remove additions before you want to use SP for real ;-)

Ian

 

[LMGroup]

Thanks for the help Ian. That's great! Problem solved and the report is running the way I wanted.