22,296
社区成员
发帖
与我相关
我的任务
分享函数查询where条件变量
大家好，现如今我有这样一个存储过程：
然后我代码传过来变量，我的存储过程通过判断变量是否为空，来决定是否添加这条where条件。
如：片段
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
但是现在的话，我如果不知道如何判断为空，难道要N多判断叠加进去吗。
...全文
请发表友善的回复…
发表回复
名字委婉点 2013-11-19
- 打赏
- 举报
AdministrativeUnit是父节点编码 比如00001
子节点编码规律为 父节点 + 子节点的那一段 如：00001001
@AdministrativeUnitChildFlag是否包含下级
如果包含下级就 like 00001
如果不包含下级就 = 00001
名字委婉点 2013-11-19
- 打赏
- 举报
存储过程的意思是：
如果@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
--小F-- 2013-11-19
- 打赏
- 举报
不怕条件多。条件判断多并不意味着查询语句不够优化。
名字委婉点 2013-11-19
- 打赏
- 举报
我加你关注了，你的意思是，你要把这个存储过程改成 函数？ 现在有什么问题呢[/quote]
对，就是想把存储过程改成函数。
现在问题是函数无法多判断
像存储过程这样，比如我这个 @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,'') + '%'
LongRui888 2013-11-19
- 打赏
- 举报
我加你关注了，你的意思是，你要把这个存储过程改成 函数？ 现在有什么问题呢
名字委婉点 2013-11-19
- 打赏
- 举报
@dawugui @水族杰纶 @孟子E章 @wuyuq
名字委婉点 2013-11-18
- 打赏
- 举报
建议给出完整代码,才好帮你改写喔.
[/quote]
[/quote]
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
唐诗三百首 2013-11-18
- 打赏
- 举报
建议给出完整代码,才好帮你改写喔.
名字委婉点 2013-11-18
- 打赏
- 举报
我把下面查询部分没大用的给删掉了。。。
观察的比较仔细名字委婉点 2013-11-18
- 打赏
- 举报
SET @sqlWhere += ' AND Key like ''%'+isnull(@MeterKey,'')+'%'' '唐诗三百首 2013-11-18
- 打赏
- 举报
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.
名字委婉点 2013-11-18
- 打赏
- 举报
好，我试试
sdhp 2013-11-18
- 打赏
- 举报
BuildingRegion like case @BuildingRegionChildFlag when 1 then '%'+ isnull(@BuildingRegion,'') + '%' else isnull(@BuildingRegion,'%') end
这样吧
sdhp 2013-11-18
- 打赏
- 举报
嗯，我写的这段有bug，容我再想想
sdhp 2013-11-18
- 打赏
- 举报
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
名字委婉点 2013-11-18
- 打赏
- 举报
@ap0405140
名字委婉点 2013-11-18
- 打赏
- 举报
@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)--展示无用
