我现在正在调试一个创建了很多临时表来倒来倒去的存储过程。有点无所适从,我想了一下,是不是可以用下面几步来做?
1. 外部临时变量比如@PartNumber,@start, @end先在一开始就赋值,这样做应该是正确的吧?
2. 把临时表的#表名换成以‘tmp表名’,这样就可以开多个查询窗口,让每个查询窗口监视一个表的更新状况。而不会因为#临时表而查不到内容。这样做行不行的通?
3. 为了调试,在每个表的insert或update之前和之后都select一次,看看表的效果。这样做可以吧?
4. 这段代码没有循环语句,我想问的是:如果碰到循环语句,在ssms里有无逐步监控的功能?如果有怎么设置?
谢谢!DECLARE
@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 = @PNINSERT 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,TestDescriptionINSERT 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
,TestDescriptionINSERT 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
,TestDescriptionINSERT INTO #TblFinal
(ProductionOrder
,PartNumber
,PartDesc
,TestDesc
,PassQty)
SELECT *
FROM
#TblPassUPDATE #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.TestDescUPDATE #TblFinal
SET TestStart = startDate
FROM
#TblFinal t1
LEFT OUTER JOIN
#TblTestSteps t2
ON
t1.TestDesc = t2.TestDesc
AND
t1.ProductionOrder = t2.ProductionOrderUPDATE #TblFinal
SET FailQty = (CASE WHEN FailQty IS NULL THEN 0 ELSE FailQty END)UPDATE #TblFinal
SET Total = PassQty + FailQtyUPDATE #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.ProductionOrderSELECT *
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][/code]
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货