How do I get a SQL function to return a list to be used by the IN statement in a WHERE clause?

Question

Hey guys

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)

Answer 1

Well, if getPeriodsInRange is actually just a SQL query, you could just use a nested query instead of a procedure (you said simpler!):

Where P.PeriodID in (Select PeriodID from MyDateTable WHERE pd > SomeMinValue AND pd < SomeMaxValue)

Answer 2

Whatever you are doing in the GetPeriodsInRange function - cant you just pull that into the main query like...

Where P.PeriodID in
(
 select PeriodID from...etc
)

This wont work if the function is doing more complicated stuff! Maybe you can let us see what is in the function?

Answer 3

I'd write a table-valued function that returns a one-column table with your Periods. Then you can inner-join this table-valued result. This way it should be executed only once, not for each row (but I'm not absolutely sure about this).

Further reading: Table-Valued User-Defined Functions (MSDN)

Cheers Matthias

Answer 4

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.

Answer 5

First, you are not "thinking in sets". The SQL language has but one data structure, being the table i.e. rows of columns. A column's data type must be scalar to satisfy first normal form. So there are no arrays, lists, etc.

While you could possibly generate a list of PeriodID values then squirt the list into a SQL query's text then use dynamic SQL capabilities to execute it, this isn't the way to go.

Consider your

Where P.PeriodID in (36, 37)

...can be rewritten as

Where P.PeriodID IN (
                     SELECT 36
                     UNION ALL
                     SELECT 37
                    )

...or even

WHERE EXISTS (
              SELECT * 
                FROM (
                      SELECT 36
                      UNION ALL
                      SELECT 37
                     ) AS DT1 (PeriodID)
               WHERE P.PeriodID = DT1.PeriodID
              );

...and you will hopefully be getting the idea of working with tables rather than lists of identifiers.

That said, taking a step back, this looks like a case where you are better off working with the natural key for periods, being the compound (StartDate, EndDate), rather than the artifical/surrogate key PeriodID. Working with just the dates means you merely have to fnd the overlap e.g.

SELECT P1.PeriodID
  FROM Periods AS P1
 WHERE CASE 
          WHEN @StartDate > P1.StartDate THEN @StartDate 
          ELSE P1.StartDate
       END 
       <= 
       CASE 
          WHEN @EndDate > P1.EndDate THEN P1.EndDate 
          ELSE @EndDate 
       END;