如何获取SQL函数以返回WHERE子句中IN语句使用的列表？(How do I get a SQL function to return a list to be used by the IN sta

如何获取SQL函数以返回WHERE子句中IN语句使用的列表？(How do I get a SQL function to return a list to be used by the IN statement in a WHERE clause?)

我有一个复杂的SQL查询，需要进一步过滤。 WHERE子句的一部分如下所示：

Where P.PeriodID in (36, 37)

我需要让它看起来更像这样：

Where P.PeriodID in dbo.GetPeriodsInRange(@startDate, @endDate)

该函数必须返回IN语句要使用的PeriodID列表。 我真的很想写函数，所以我需要一些帮助。 另外，我不知道如何处理边缘情况，比如说在指定的日期范围内没有句点。

上面的函数不需要为每一行进行求值。 每行都是相同的，因此可能会执行一些优化，可能在执行查询之前。

我很确定我在这里打破了几个“最佳实践”，所以如果有更好的方法，请指出它们。 但是，性能不是问题所在，所以我愿意牺牲性能来支持简单性。

我的问题适用于T-SQL（MS SQL Server 2000/2005）

I have a complex SQL Query, that needs to be filtered further. Part of the WHERE clause looks like this:

Where P.PeriodID in (36, 37)

I need to get it to look more like this:

Where P.PeriodID in dbo.GetPeriodsInRange(@startDate, @endDate)

The function must return a list of PeriodIDs to be used by the IN statement. I really suck at writing functions, so I need some assistance. Also, I'm not sure how to deal with edge cases, say if there are no periods in the specified date range.

The function above doesn't need to get evaluated for each row. It will be the same for each row, so there is probably some optimisation that can be done, maybe before the query is executed.

I'm pretty sure I'm breaking several "best practices" here, so please point them out to me if there is a better way to do this. However, performance is not an issue, so I'm willing to sacrifice performance in favour of simplicity.

My question applies to T-SQL (MS SQL Server 2000/2005)

最满意答案

我的解决方案是创建一个Mudu建议的功能。 我用这个 。

从链接：

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[ufn_GenerateIntegers] ( @MinValue INT, @MaxValue INT ) RETURNS @Integers TABLE ( [IntValue] INT ) AS BEGIN WHILE @MinValue <= @MaxValue BEGIN INSERT INTO @Integers ( [IntValue] ) VALUES ( @MinValue ) SET @MinValue = @MinValue + 1 END RETURN END GO

之后，我使用这样的内部联接：

use tempdb select *into #test from ( select 1 as n, 35 as periodId union select 2 as n, 36 as periodId union select 1 as n, 36 as periodId union select 2 as n, 37 as periodId ) a select p.* from #test p inner join [dbo].[ufn_GenerateIntegers](36, 37) on [IntValue] = periodId

您可以生成一个String然后执行它，但我认为这不是很好的建议。

My solution would be to create a function as Mudu suggested. I m using this.

From the link:

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[ufn_GenerateIntegers] ( @MinValue INT, @MaxValue INT ) RETURNS @Integers TABLE ( [IntValue] INT ) AS BEGIN WHILE @MinValue <= @MaxValue BEGIN INSERT INTO @Integers ( [IntValue] ) VALUES ( @MinValue ) SET @MinValue = @MinValue + 1 END RETURN END GO

After that i use a inner join like this:

use tempdb select *into #test from ( select 1 as n, 35 as periodId union select 2 as n, 36 as periodId union select 1 as n, 36 as periodId union select 2 as n, 37 as periodId ) a select p.* from #test p inner join [dbo].[ufn_GenerateIntegers](36, 37) on [IntValue] = periodId

You could generate a String and then execute it but it is not very advised here, I think.