函数查询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
