疑难问题
函数查询where条件变量
墨衍会员 · AI 创作全网分发 墨衍智能分发 通过墨衍一键同步至各平台 可同步平台 CSDN微信公众号微博知乎掘金百家号博客园抖音小红书 你的文章也可以这样分发 墨衍会员 ¥399起/年，写一次发全网
我也要 关注 + 加入社区大家好，现如今我有这样一个存储过程：
然后我代码传过来变量，我的存储过程通过判断变量是否为空，来决定是否添加这条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多判断叠加进去吗。
...全文
相关推荐
SqlServer参数化查询之where in和like实现详解
另一种方法是利用T-SQL的`PATINDEX`函数，但这也可能导致索引失效，尤其是当处理大数据量时。
Laravel where 查询技巧
Laravel where 查询技巧...
MySQL左联多表查询where条件写法示例
如果where条件直接放在主查询中，可能会改变连接的性质，导致非预期的结果。而通过子查询预筛选，我们可以确保where条件只影响子查询的结果，而不影响左连接的整体行为，从而得到更精确的查询结果。
Sql Server参数化查询之where in和like实现详解
参数化查询是提高查询性能和防止SQL注入攻击的有效方法之一。where in和like的参数化查询实现可以使用CHARINDEX函数、临时表、XML和DataTable等方法来实现。
详解Python中where()函数的用法
首先，`where()`函数的基本语法是`numpy.where(condition, x, y)`，其中`condition`是一个数组，`x`和`y`都是可选参数。
