帮忙看看一个存储过程SQL字符串
本帖最后由 huanggreat 于 2013-01-21 09:03:22 编辑
USE [RCollarGarmentMES]
GO
/****** Object: StoredProcedure [dbo].[PRO_GETReport] Script Date: 01/21/2013 08:22:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[PRO_GETReport]
(@StartDate varchar(50),--起始日期
@EndDate varchar(50),
@RType int,--
@StartStepCode VARCHAR(20),--开始工序
@LimitDay INT) --多少天？？？
AS
DECLARE @GX INT
DECLARE @GXCount INT
DECLARE @StepCode VARCHAR(20)
DECLARE @NextStepCode VARCHAR(20)
DECLARE @LS_StepCode VARCHAR(20)
DECLARE @LS_Total VARCHAR(20)
DECLARE @StrSql VARCHAR(1000)
DECLARE @TableName VARCHAR(10)
DECLARE @NextTableName VARCHAR(10)
SET @GXCount=0
SET @GX=0
SET @StepCode=@StartStepCode
SET @NextStepCode=@StartStepCode
SELECT @GXCount=COUNT(*) FROM GXOrder WHERE aType='1'
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id =object_id('tempdb.dbo.#temp'))
DROP TABLE tempdb.#temp
CREATE TABLE #temp (StepCode VARCHAR(20) NOT NULL ,Total VARCHAR(20) NOT NULL)
--BEGIN
SET @TableName='GX'+@StepCode
SELECT TOP 1 @NextStepCode=NextStepCode FROM GXOrder WHERE StepCode=@StepCode
SET @NextTableName='GX'+@NextStepCode
SET @LS_StepCode=@StepCode
SET @StrSql='SELECT '+@LS_Total+'=COUNT(*) FROM '+@TableName+'
WHERE BrushDate>='''+@StartDate+''' AND BrushDate<'''+@EndDate+'''
AND OdID NOT IN(SELECT OdID FROM '+@NextTableName+'
)'
PRINT @StrSql
--EXEC(@StrSql)
SET @StepCode=@NextStepCode
--INSERT INTO #temp VALUES(@LS_StepCode,@LS_Total)
--SET @StepCode=@NextStepCode
--SELECT TOP 1 @StepCode=NextStepCode FROM GXOrder WHERE StepCode=@StepCode
SET @GX=@GX+1
--END
--SELECT * FROM #temp
PRINT @TableName
PRINT @NextTableName
PRINT @StartDate
PRINT @EndDate
GO
EXEC PRO_GETReport '2013-01-15 9:00:00','2013-01-15 9:59:59',1,'321',7
--WHERE BrushDate<=DATEADD(DAY,'+Convert(INT,@LimitDay)+','''+@EndDate+''')
SET @StrSql='SELECT '+@LS_Total+'=COUNT(*) FROM '+@TableName+' WHERE BrushDate>='+char(39)+@StartDate+char(39)+' AND BrushDate<'+char(39)+@EndDate+char(39)+' AND OdID NOT IN(SELECT OdID FROM '+@NextTableName+' )'
