多临时表的临时存储过程的调试，很苦恼
我现在正在调试一个创建了很多临时表来倒来倒去的存储过程。有点无所适从，我想了一下，是不是可以用下面几步来做？
1. 外部临时变量比如@PartNumber，@start, @end先在一开始就赋值，这样做应该是正确的吧？
2. 把临时表的#表名换成以‘tmp表名’，这样就可以开多个查询窗口，让每个查询窗口监视一个表的更新状况。而不会因为#临时表而查不到内容。这样做行不行的通？
3. 为了调试，在每个表的insert或update之前和之后都select一次，看看表的效果。这样做可以吧？
4. 这段代码没有循环语句，我想问的是：如果碰到循环语句，在ssms里有无逐步监控的功能？如果有怎么设置？
谢谢！
DECLARE[/code]
@Index INT
,@LastIndex INT
,@PN VARCHAR(25)
SET @Index = (SELECT CHARINDEX(':',@PartNumber))
SET @LastIndex = (SELECT CHARINDEX('(',@PartNumber))
SET @PN = (CASE WHEN LEN(@PartNumber) > 25 THEN SUBSTRING(@PartNumber,@Index+2,((@LastIndex-4)-(@Index -1))) ELSE @PartNumber END)
CREATE TABLE #TblPass
(ProductionOrder VARCHAR(25)
,PartNumber VARCHAR(25)
,PartDesc VARCHAR(100)
,TestDesc VARCHAR(100)
,PassQty INT)
CREATE TABLE #TblFail
(ProductionOrder VARCHAR(25)
,PartNumber VARCHAR(25)
,PartDesc VARCHAR(100)
,TestDesc VARCHAR(100)
,FailQty INT)
CREATE TABLE #TblFinal
(ProductionOrder VARCHAR(25)
,PartNumber VARCHAR(25)
,PartDesc VARCHAR(100)
,TestDesc VARCHAR(100)
,PassQty INT
,FailQty INT
,Total INT
,Yield FLOAT
,TestStart DATETIME
,ProdOrderStart DATETIME)
CREATE TABLE #TblTestSteps
(ProductionOrder VARCHAR(25)
,TestDesc VARCHAR(100)
,StartDate DATETIME)
CREATE TABLE #TblPN
(JDSUPartNumber VARCHAR(50)
,PartNumber VARCHAR(50))
INSERT INTO #TblPN
SELECT
JDSUPartNumber
,BEIPartNumber
FROM
TblPartLookUp
WHERE
JDSUPartNumber = @PN
INSERT INTO #TblTestSteps
SELECT
ProductionOrder
,TestDescription
,MIN(TestDateTime)
FROM
TblTestData
WHERE
ProductionOrder IN (SELECT
ProductionOrder
FROM
TblProductionOrders
WHERE
JDSUPartNumber = @PN
AND
StartTime BETWEEN @Start AND @End)
GROUP BY
ProductionOrder,TestDescription
INSERT INTO #TblPass
SELECT
ProductionOrder
,PartNumber
,PartDescription
,TestDescription
,COUNT(DISTINCT SerialNumber) AS QTY
FROM
TblTestData
WHERE
SerialNumber IN (SELECT
SerialNumber
FROM
TblSerialNumbers
WHERE
StartDate BETWEEN @Start AND @End)
AND
PartNumber IN (SELECT
PartNumber
FROM #TblPN)
AND
CycleNumber = 1
AND
Result = 'P'
GROUP BY
ProductionOrder
,PartNumber
,PartDescription
,TestDescription
INSERT INTO #TblFail
SELECT
ProductionOrder
,PartNumber
,PartDescription
,TestDescription
,COUNT(DISTINCT SerialNumber) AS QTY
FROM
TblTestData
WHERE
SerialNumber IN (SELECT
SerialNumber
FROM
TblSerialNumbers
WHERE
StartDate BETWEEN @Start AND @End)
AND
PartNumber IN (SELECT
PartNumber
FROM
#TblPN)
AND
CycleNumber = 1
AND
Result = 'F'
GROUP BY
ProductionOrder
,PartNumber
,PartDescription
,TestDescription
INSERT INTO #TblFinal
(ProductionOrder
,PartNumber
,PartDesc
,TestDesc
,PassQty)
SELECT *
FROM
#TblPass
UPDATE #TblFinal
SET FailQty = t2.FailQty
FROM
#TblFinal t1
LEFT OUTER JOIN
#TblFail t2
ON
t1.PartNumber = t2.PartNumber
AND
t1.ProductionOrder = t2.ProductionOrder
AND
t1.TestDesc = t2.TestDesc
UPDATE #TblFinal
SET TestStart = startDate
FROM
#TblFinal t1
LEFT OUTER JOIN
#TblTestSteps t2
ON
t1.TestDesc = t2.TestDesc
AND
t1.ProductionOrder = t2.ProductionOrder
UPDATE #TblFinal
SET FailQty = (CASE WHEN FailQty IS NULL THEN 0 ELSE FailQty END)
UPDATE #TblFinal
SET Total = PassQty + FailQty
UPDATE #TblFinal
SET Yield = ROUND(CAST(PassQty AS FLOAT)/Total,4)
UPDATE #TblFinal
SET ProdOrderStart = StartTime
FROM
#TblFinal t1
LEFT OUTER JOIN
TblProductionOrders t2
ON
t1.ProductionOrder = t2.ProductionOrder
SELECT *
FROM
#TblFinal
ORDER BY StartTime DESC)AS P) AND (ProductionOrder NOT LIKE '%/_%' ESCAPE '/')
WHERE
ProductionOrder NOT LIKE '%/_%' ESCAPE '/'
ORDER BY
ProdOrderStart,TestStart
DROP TABLE #TblPass
DROP TABLE #TblFail
DROP TABLE #TblFinal
DROP TABLE #TblTestSteps[code=sql]
