22,209
社区成员
发帖
与我相关
我的任务
分享
IF @MeterKey IS NOT NULL
SET @sqlWhere += ' AND Key like ''%'+@MeterKey+'%'' '
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[usp_Meter_GetManuallyRecordReportDt]
@MeterType VARCHAR(2) = NULL,
@MeterName VARCHAR(200) = NULL,
@MeterKey VARCHAR(200) = NULL,
@AdministrativeUnit VARCHAR(20) = NULL,
@BuildingRegion VARCHAR(20) = NULL,
@Usesign VARCHAR(20) = NULL,
@AdministrativeUnitChildFlag BIT,
@BuildingRegionChildFlag BIT,
@UsesignChildFlag BIT,
@AllMeterFlag BIT,
@CollectorIP VARCHAR(50) = NULL
AS
BEGIN
CREATE TABLE #MyTempTable (Tabno INT PRIMARY KEY)
DECLARE @sql NVARCHAR(MAX)
DECLARE @sqlWhere VARCHAR(500)
BEGIN
SET @sqlWhere = ' 1=1 AND Type = '''+@MeterType+''' '
IF @MeterKey IS NOT NULL
SET @sqlWhere += ' AND Key like ''%'+@MeterKey+'%'' '
IF @MeterName IS NOT NULL
SET @sqlWhere += ' AND Name like ''%'+@MeterName+'%'' '
IF @AdministrativeUnit IS NOT NULL
BEGIN
IF @AdministrativeUnitChildFlag=1
SET @sqlWhere += ' AND AdministrativeUnit like ''%' + @AdministrativeUnit + '%'' '
ELSE
SET @sqlWhere += ' AND AdministrativeUnit =''' + @AdministrativeUnit + ''' '
END
IF @BuildingRegion IS NOT NULL
BEGIN
IF @BuildingRegionChildFlag=1
SET @sqlWhere += ' AND BuildingRegion like ''%' + @BuildingRegion + '%'' '
ELSE
SET @sqlWhere += ' AND BuildingRegion =''' + @BuildingRegion + ''' '
END
IF @Usesign IS NOT NULL
BEGIN
IF @UsesignChildFlag=1
SET @sqlWhere += ' AND Usesign like ''%' + @Usesign + '%'' '
ELSE
SET @sqlWhere += ' AND Usesign =''' + @Usesign + ''' '
END
IF @CollectorIP IS NOT NULL
SET @sqlWhere += ' AND IP like ''%' + @CollectorIP + '%'' '
END
SET @sql='INSERT INTO #MyTempTable(Tabno) select Tabno from [dbo].[T_EC_AmmeterBaseInformation] WHERE ' + @sqlWhere
EXEC (@sql)
BEGIN
SET @sql='SELECT b.Tabno,
b.Name
FROM dbo.T_EC_AmmeterBaseInformation b
left join #MyTempTable tmp on tmp.Tabno = b.Tabno
WHERE b.Tabno = tmp.Tabno
EXEC (@sql)
END
DROP TABLE #MyTempTable
END
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Meter_CalculateMeterRealData]
(
@MeterType VARCHAR(2),
@AllMeterFlag BIT,
@MeterName VARCHAR(200),
@MeterKey VARCHAR(200),
@AdministrativeUnit VARCHAR(20) = NULL,
@AdministrativeUnitChildFlag BIT = 0,
@BuildingRegion VARCHAR(20) = NULL,
@BuildingRegionChildFlag BIT = 0,
@Usesign VARCHAR(20) = NULL,
@UsesignChildFlag BIT = 0,
@CollectorIP VARCHAR(50)
)
RETURNS @Table TABLE ( Tabno INT,Name VARCHAR(50),InstallPosition VARCHAR(100),
Usesign VARCHAR(100),BuildingRegion VARCHAR(100),AdministrativeUnit VARCHAR(100),
F_ModifyTime DATETIME,IP VARCHAR(50),VALUE DECIMAL(18,4))
AS
BEGIN
DECLARE @Tabno INT
IF @AdministrativeUnit IS NOT NULL --如果行政单位不为空
BEGIN
IF @AdministrativeUnitChildFlag=1 --如果包含下级
BEGIN
DECLARE myCusor CURSOR FOR
SELECT tabno FROM T_EC_AmmeterBaseInformation WHERE isreal=1
AND [TYPE] = @MeterType
AND [Name] LIKE '%' + @MeterName + '%'
--AND [KEY] LIKE '%' + @MeterKey + '%'
--AND [IP] LIKE '%' + @CollectorIP + '%'
AND [AdministrativeUnit] LIKE '%' + @AdministrativeUnit + '%'
END
ELSE
BEGIN
DECLARE myCusor CURSOR FOR
SELECT tabno FROM T_EC_AmmeterBaseInformation WHERE isreal=1
AND [TYPE] = @MeterType
AND [Name] LIKE '%' + @MeterName + '%'
--AND [KEY] LIKE '%' + @MeterKey + '%'
--AND [IP] LIKE '%' + @CollectorIP + '%'
AND [AdministrativeUnit] = @AdministrativeUnit
END
END
OPEN myCusor
FETCH NEXT FROM myCusor INTO @Tabno
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT @Table
SELECT b.Tabno,
b.Name,
FROM dbo.T_EC_AmmeterBaseInformation b
WHERE b.Tabno IN (@Tabno)
FETCH NEXT FROM myCusor INTO @Tabno
END
CLOSE myCusor
DEALLOCATE myCusor
RETURN
END
IF @AdministrativeUnit IS NOT NULL
BEGIN
IF @AdministrativeUnitChildFlag=1
SET @sqlWhere += ' AND AdministrativeUnit like ''%' + @AdministrativeUnit + '%'' '
ELSE
SET @sqlWhere += ' AND AdministrativeUnit =''' + @AdministrativeUnit + ''' '
END
IF @BuildingRegion IS NOT NULL
BEGIN
IF @BuildingRegionChildFlag=1
SET @sqlWhere += ' AND BuildingRegion like ''%' + @BuildingRegion + '%'' '
ELSE
SET @sqlWhere += ' AND BuildingRegion =''' + @BuildingRegion + ''' '
END
IF @Usesign IS NOT NULL
BEGIN
IF @UsesignChildFlag=1
SET @sqlWhere += ' AND Usesign like ''%' + @Usesign + '%'' '
ELSE
SET @sqlWhere += ' AND Usesign =''' + @Usesign + ''' '
END
IF @AdministrativeUnit IS NOT NULL
BEGIN
IF @AdministrativeUnitChildFlag=1
SET @sqlWhere += ' AND AdministrativeUnit like ''%' +@AdministrativeUnit+'%'' '
ELSE
SET @sqlWhere += ' AND AdministrativeUnit =''' + @AdministrativeUnit + ''' '
END
IF @AdministrativeUnit=1 --如果包含下级
DECLARE myCusor CURSOR FOR
SELECT tabno FROM T_EC_AmmeterBaseInformation WHERE isreal=1
AND [TYPE] = @MeterType
AND [Name] LIKE '%' + ISNULL(@MeterName,'') + '%'
AND [KEY] LIKE '%' + ISNULL(@MeterKey,'') + '%'
AND [AdministrativeUnit] LIKE '%' + ISNULL(@AdministrativeUnit,'') + '%'
AND [BuildingRegion] LIKE '%' + ISNULL(@BuildingRegion,'') + '%'
AND [Usesign] LIKE '%' + ISNULL(@Usesign,'') + '%'
AND [IP] LIKE '%' + ISNULL(@CollectorIP,'') + '%'
else
DECLARE myCusor CURSOR FOR
SELECT tabno FROM T_EC_AmmeterBaseInformation WHERE isreal=1
AND [TYPE] = @MeterType
AND [Name] LIKE '%' + ISNULL(@MeterName,'') + '%'
AND [KEY] LIKE '%' + ISNULL(@MeterKey,'') + '%'
AND [AdministrativeUnit] = ISNULL(@AdministrativeUnit,'')
AND [BuildingRegion] LIKE '%' + ISNULL(@BuildingRegion,'') + '%'
AND [Usesign] LIKE '%' + ISNULL(@Usesign,'') + '%'
AND [IP] LIKE '%' + ISNULL(@CollectorIP,'') + '%'
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Meter_CalculateMeterRealData]
(
@AllMeterFlag BIT,
@MeterType VARCHAR(2),
@MeterName VARCHAR(200),
@MeterKey VARCHAR(200),
@AdministrativeUnit VARCHAR(20) = NULL,
@AdministrativeUnitChildFlag BIT = 0,
@BuildingRegion VARCHAR(20) = NULL,
@BuildingRegionChildFlag BIT = 0,
@Usesign VARCHAR(20) = NULL,
@UsesignChildFlag BIT = 0,
@CollectorIP VARCHAR(50)
)
RETURNS @Table TABLE ( Tabno INT,Name VARCHAR(50),InstallPosition VARCHAR(100),
Usesign VARCHAR(100),BuildingRegion VARCHAR(100),AdministrativeUnit VARCHAR(100),
F_ModifyTime DATETIME,IP VARCHAR(50),VALUE DECIMAL(18,4))
AS
BEGIN
DECLARE @Tabno INT
IF @AdministrativeUnit=1 --如果包含下级
DECLARE myCusor CURSOR FOR
SELECT tabno FROM T_EC_AmmeterBaseInformation WHERE isreal=1
AND [TYPE] = @MeterType
AND [Name] LIKE '%' + ISNULL(@MeterName,'') + '%'
AND [KEY] LIKE '%' + ISNULL(@MeterKey,'') + '%'
AND [AdministrativeUnit] LIKE '%' + ISNULL(@AdministrativeUnit,'') + '%'
AND [BuildingRegion] LIKE '%' + ISNULL(@BuildingRegion,'') + '%'
AND [Usesign] LIKE '%' + ISNULL(@Usesign,'') + '%'
AND [IP] LIKE '%' + ISNULL(@CollectorIP,'') + '%'
else
DECLARE myCusor CURSOR FOR
SELECT tabno FROM T_EC_AmmeterBaseInformation WHERE isreal=1
AND [TYPE] = @MeterType
AND [Name] LIKE '%' + ISNULL(@MeterName,'') + '%'
AND [KEY] LIKE '%' + ISNULL(@MeterKey,'') + '%'
AND [AdministrativeUnit] = ISNULL(@AdministrativeUnit,'')
AND [BuildingRegion] LIKE '%' + ISNULL(@BuildingRegion,'') + '%'
AND [Usesign] LIKE '%' + ISNULL(@Usesign,'') + '%'
AND [IP] LIKE '%' + ISNULL(@CollectorIP,'') + '%'
-- IF @BuildingRegionChildFlag=1 --如果包含下级
-- BEGIN
-- DECLARE myCusor CURSOR FOR
-- SELECT tabno FROM T_EC_AmmeterBaseInformation WHERE isreal=1
-- AND [TYPE] = @MeterType
-- AND [Name] LIKE '%' + @MeterName + '%'
-- --AND [KEY] LIKE '%' + @MeterKey + '%'
-- --AND [IP] LIKE '%' + @CollectorIP + '%'
-- AND [BuildingRegion] LIKE '%' + @BuildingRegion + '%'
-- END
-- ELSE
-- BEGIN
-- DECLARE myCusor CURSOR FOR
-- SELECT tabno FROM T_EC_AmmeterBaseInformation WHERE isreal=1
-- AND [TYPE] = @MeterType
-- AND [Name] LIKE '%' + @MeterName + '%'
-- --AND [KEY] LIKE '%' + @MeterKey + '%'
-- --AND [IP] LIKE '%' + @CollectorIP + '%'
-- AND [BuildingRegion] = @BuildingRegion
-- END
--END
OPEN myCusor
FETCH NEXT FROM myCusor INTO @Tabno
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT @Table
SELECT b.Tabno,
b.Name,
b.InstallPosition,
tde.F_EnergyItemName AS Usesign,
teb.Name AS BuildingRegion,
tea.Name AS AdministrativeUnit,
c.F_ModifyTime,
b.IP,
(CASE Isaccumulative
WHEN 1 THEN
(CASE WHEN CTFlag = 1 THEN CTdividend / CTDivisor ELSE 1 END) *
(CASE WHEN Flag = 3 AND PhaseFlag = 1 THEN Flagvalue ELSE 1 END) / CONVERT(DECIMAL(10, 5), b.DecimalAdjust)
WHEN 0 THEN
(F_Newvalue-b.MinEffectiveValue)/(SELECT ((MaxEffectiveValue - MinEffectiveValue) / (RangMax - RangMin)) AS VALUE) + b.RangMin ELSE 1 END) AS VALUE
FROM dbo.T_EC_AmmeterBaseInformation b JOIN dbo.T_EC_EnergyTemporaryValues c ON b.Tabno=c.F_MeterCode
LEFT JOIN T_DT_EnergyItemDict tde ON tde.F_EnergyItemCode = b.Usesign
LEFT JOIN T_EC_BuildingRegion teb ON teb.Nodecode = b.BuildingRegion
LEFT JOIN T_EC_AdministrativeUnit tea ON tea.Nodecode = b.AdministrativeUnit
WHERE b.Tabno IN (@Tabno)
ORDER BY c.F_ModifyTime,c.F_MeterCode
FETCH NEXT FROM myCusor INTO @Tabno
END
CLOSE myCusor
DEALLOCATE myCusor
RETURN
END
SET @sqlWhere += ' AND Key like ''%'+isnull(@MeterKey,'')+'%'' '
恩,这部分改成isnull了缩减不少代码,非常感谢。
下班部分的话说语法附近有错误。
原来也这样判断过 也没通过
@MeterType VARCHAR(2), --仪表类型
@AllMeterFlag BIT, --暂时无用
@MeterName VARCHAR(200),--仪表名称 模糊查询
@MeterKey VARCHAR(200), --物理编码 模糊查询
@AdministrativeUnit VARCHAR(20) = NULL, --行政单位,根据@AdministrativeUnitChildFlag是1还是0来判断:如果1模糊查询,如果0=查询
@AdministrativeUnitChildFlag BIT = 0,--是否包含下级
@BuildingRegion VARCHAR(20) = NULL,--地理区域 :同行政单位查询方式
@BuildingRegionChildFlag BIT = 0,--是否包含下级
@Usesign VARCHAR(20) = NULL,--用途 :同行政单位查询方式
@UsesignChildFlag BIT = 0,--是否包含下级
@CollectorIP VARCHAR(50)--展示无用