IF @MeterKey IS NOT NULL SET @sqlWhere += ' AND Key like ''%'+@MeterKey+'%'' '这个不能直接用SET @sqlWhere += ' AND Key like ''%'+isnull(@MeterKey,'')+'%'' '来代替么IF @BuildingRegion IS NOT NULL BEGIN IF @BuildingRegionChildFlag=1 SET @sqlWhere += ' AND BuildingRegion like ''%' + @BuildingRegion + '%'' ' ELSE SET @sqlWhere += ' AND BuildingRegion =''' + @BuildingRegion + ''' ' END这种可以替换成 BuildingRegion like case @BuildingRegionChildFlag when 1 then '%'+ isnull(@BuildingRegion,'') + '%' else @BuildingRegion end
嗯,我写的这段有bug,容我再想想
BuildingRegion like case @BuildingRegionChildFlag when 1 then '%'+ isnull(@BuildingRegion,'') + '%' else isnull(@BuildingRegion,'%') end这样吧
LZ所写函数代码56行语法有误喔,@Table表有9个字段,这里只给2个字段的值,会报错的. Msg 213, Level 16, State 1, Line 8 Column name or number of supplied values does not match table definition.
SET @sqlWhere += ' AND Key like ''%'+isnull(@MeterKey,'')+'%'' ' 恩,这部分改成isnull了缩减不少代码,非常感谢。 下班部分的话说语法附近有错误。 原来也这样判断过 也没通过
建议给出完整代码,才好帮你改写喔. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GOALTER 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
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,'') + '%'
存储过程的意思是: 如果@AdministrativeUnit不为空,sql where条件加上这条语句,根据@AdministrativeUnitChildFlag=1还是0来判断条件是'='还是'like'而后还有两个这样判断的语句。而函数中可以判断是否为空了,如果为空就不会where @AdministrativeUnit这个条件了,但我还得判断一下@AdministrativeUnitChildFlag=1还是0来判断应该是'like'还是'='。 而我后面还有@BuildingRegionFlag和 @UsesignFlag需要判断是1或0来判断是like还是= 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
@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)--展示无用
SET @sqlWhere += ' AND Key like ''%'+@MeterKey+'%'' '这个不能直接用SET @sqlWhere += ' AND Key like ''%'+isnull(@MeterKey,'')+'%'' '来代替么IF @BuildingRegion IS NOT NULL
BEGIN
IF @BuildingRegionChildFlag=1
SET @sqlWhere += ' AND BuildingRegion like ''%' + @BuildingRegion + '%'' '
ELSE
SET @sqlWhere += ' AND BuildingRegion =''' + @BuildingRegion + ''' '
END这种可以替换成
BuildingRegion like case @BuildingRegionChildFlag when 1 then '%'+ isnull(@BuildingRegion,'') + '%' else @BuildingRegion end
Msg 213, Level 16, State 1, Line 8
Column name or number of supplied values does not match table definition.
恩,这部分改成isnull了缩减不少代码,非常感谢。
下班部分的话说语法附近有错误。
原来也这样判断过 也没通过
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOALTER 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
我加你关注了,你的意思是,你要把这个存储过程改成 函数? 现在有什么问题呢对,就是想把存储过程改成函数。现在问题是函数无法多判断
像存储过程这样,比如我这个 @AdministrativeUnit如果不为空的话,我在判断@AdministrativeUnitChildFlag是0还是1,然后加上这条and语句,我后面还有两个这样的判断,如果@BuildingRegion不为空 也加上这个and 语句。
而函数没有这么灵活啊无法判断是否为空就加上这个条件,
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,'') + '%'
存储过程的意思是:
如果@AdministrativeUnit不为空,sql where条件加上这条语句,根据@AdministrativeUnitChildFlag=1还是0来判断条件是'='还是'like'而后还有两个这样判断的语句。而函数中可以判断是否为空了,如果为空就不会where @AdministrativeUnit这个条件了,但我还得判断一下@AdministrativeUnitChildFlag=1还是0来判断应该是'like'还是'='。
而我后面还有@BuildingRegionFlag和 @UsesignFlag需要判断是1或0来判断是like还是= 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
如果包含下级就 like 00001
如果不包含下级就 = 00001