-- Description: 按月查询动态表名(按月生成表名)数据
-- =============================================
Create PROCEDURE [dbo].[DataCsvs_QueryDoMethod]
@I_Sender NVARCHAR(200)='', --客户端执行按钮
@I_ReturnMessage NVARCHAR(MAX)='' OUTPUT, --返回的信息,支持多语言
@I_ExceptionFieldName NVARCHAR(100)='' OUTPUT, --向客户端报告引起冲突的字段
--以上变量为系统服务固定接口参数,必须在每一个DoEvent过程中实现.
@HostCoordinate NVARCHAR(50)='',
@HostName NVARCHAR(50)='',
--@StartDate datetime=null,
--@EndDate datetime=NULL,
@Mouth NVARCHAR(50)='',
@Year NVARCHAR(50)='',
@HostNumber NVARCHAR(50)=''
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @Sql NVARCHAR(MAX)
DECLARE @tablename NVARCHAR(MAX)
-- DECLARE @firstday DATETIME
--DECLARE @lastday DATETIME
--SET @firstday=CONVERT(VARCHAR(10),DATEADD(DAY,-DATEPART(DAY,GETDATE())+1,GETDATE()),23) +' 00:00:00' --当月第一天
--SET @lastday=CONVERT(VARCHAR(10),DATEADD(DAY,-1,DATEADD(MONTH,1,GETDATE()-DAY(GETDATE())+1)),23)+' 23:59:59' --当月最后一天
-- 检查查询条件
IF @HostName = '' AND @HostCoordinate = '' AND @Mouth IS NULL
BEGIN
SET @I_ReturnMessage = 'ServerMessage:请选择查询条件:设备品牌/设备产线/月份!'
RETURN -1
end
--产线不能为空
IF ISNULL(@HostCoordinate,'')=''
BEGIN
SET @I_ReturnMessage = 'ServerMessage:请选择设备产线!'
RETURN -1
end
--日期不能为空
IF ISNULL(@Mouth,'')=''
BEGIN
SET @I_ReturnMessage = 'ServerMessage:请选择需要查询的月份!'
RETURN -1
end
--日期不能为空
IF ISNULL(@Year,'')=''
BEGIN
SET @I_ReturnMessage = 'ServerMessage:请选择需要查询的年份['+@Year+']!'
RETURN -1
end
--获取数据库按月生成的表名--
SET @tablename='DataCsv'+@Year+@Mouth
--查询数据库是否存在该@tablename表名--
IF NOT EXISTS(SELECT 1 FROM SysObjects WHERE type='U' AND [name] = @tablename)
BEGIN
SET @I_ReturnMessage = 'ServerMessage:'+@Year+'年'+@Mouth +'月没有数据,请重新选择!'
RETURN -1
END
ELSE
BEGIN
--SET @Sql='SELECT * FROM '+@tablename+' ORDER BY DetectionTime ASC'
--SET @Sql='SELECT * FROM '+@tablename+' WHERE DetectionTime BETWEEN '''+CONVERT(VARCHAR(30),@firstday,21)+''' AND '''+CONVERT(VARCHAR(30),@lastday,21)+''' ORDER BY DetectionTime ASC'
SET @Sql='SELECT SYDeviceHost.HostNumber, SYDeviceHost.HostName, SYDeviceHost.HostCoordinate,
'+@tablename+'.DetectionTime, '+@tablename+'. ProductQRCode,'+@tablename+'.ProductCode,
'+@tablename+'.TestTtems, '+@tablename+'. RelativeVUnit,
'+@tablename+'.AbsoluteVUnit, '+@tablename+'. UpperLimit,
'+@tablename+'.LowerLimit, '+@tablename+'. NominalValue,
'+@tablename+'.RelatValue, '+@tablename+'. AbsoluteValue,
'+@tablename+'. States
FROM '+@tablename+' ,SYDeviceHost
WHERE SYDeviceHost.HostCoordinate ='''+@HostCoordinate+''' and SYDeviceHost.HostNumber='+@tablename+'. HostNumber ORDER BY DetectionTime ASC'
EXEC (@Sql)
END
END