CODEWITHSUNDEEP
sqlsql-servert-sql
mark b
mark b

Reputation: 224

Wanted to select from a table but it's ignoring it

I want to have a select statement that takes a table parameter of IDs. The desired behavior is that if there are no elements in the table, get all of the values but if there are elements in the table, only select those who's values match the ID parameter of the table.

My where clause looks like this:

SELECT * FROM BLAH
WHERE [TransactionDateTime] BETWEEN @BeginTransDate AND @EndTransDate AND
 ((SELECT COUNT(ID) FROM @LocationIds) = 0) OR (EXISTS (SELECT 1 FROM 
@LocationIds WHERE t.LocationId = ID))

However, the result is always bringing back all locations even where the LocationIds table contains one value and that ID matches only one value.

I can set the values such as, but it still brings back all locations:

DECLARE @LocationIds TABLE
(
  ID int
)

INSERT INTO @LocationIds (ID)
VALUES (10227)

Upvotes: 0

Views: 57

Answers (3)

reidh.olsen
reidh.olsen

Reputation: 111

Try this:

SELECT * 
FROM BLAH
WHERE [TransactionDateTime] BETWEEN @BeginTransDate AND @EndTransDate 
AND (
      (SELECT COUNT(ID) FROM @LocationIds) = 0
      OR 
      ID in (SELECT ID FROM @LocationIds)
     )

Upvotes: 1

Thom A
Thom A

Reputation: 95830

Personally, I do this as 2 queries, as the query plan for when there are rows in @LocationIds and is likely to differ enough that the reuse of the plan won't be beneficial to the other, Therefore you get:

IF (SELECT COUNT(*) FROM @LocationIds) = 0 BEGIN
    SELECT *
    FROM BLAH
    WHERE [TransactionDateTime] BETWEEN @BeginTransDate AND @EndTransDate;
END ELSE BEGIN
    SELECT *
    FROM BLAH B
    WHERE [TransactionDateTime] BETWEEN @BeginTransDate AND @EndTransDate
      AND EXISTS (SELECT 1
                  FROM @LocationIds L
                  WHERE B.LocationID = L.ID);
END

Alternatively, you could you dynamic SQL to build the query, depending on if the values of @LociationIDs is needed:

DECLARE @SQL nvarchar(MAX);

SET @SQL = N'SELECT *' + NCHAR(13) + NCHAR(10) +
           N'FROM BLAH B' + NCHAR(13) + NCHAR(10) +
           N'WHERE [TransactionDateTime] BETWEEN @BeginTransDate AND @EndTransDate' +
           CASE WHEN (SELECT COUNT(*) FROM @LocationIds) = 0 THEN ';'
                                                             ELSE NCHAR(13) + NCHAR(10) +
                                                                  N'  AND EXISTS (SELECT 1' + NCHAR(13) + NCHAR(10) +
                                                                  N'              FROM @LocationIds L' + NCHAR(13) + NCHAR(10) +
                                                                  N'              WHERE B.LocationID = L.ID);'
           END;

EXEC sp_executesql @SQL, N'@BeginTransDate date, @EndTransDate date', @BeginTransDate = @BeginTransDate, @EndTransDate = @EndTransDate;

Note I have guessed that @BeginTransDate and @EndTransDate are a date, and not a datetime. If they are the latter, then that query is unlikely to work as you want anyway, and you would be better of using the logic below:

WHERE TransactionDateTime >= @BeginTransDate
  AND TransactionDateTime < DATEADD(DAY, 1, @EndTransDate)

Upvotes: 2

Eric
Eric

Reputation: 3257

You are missing a pair brackets.

WHERE [TransactionDateTime] BETWEEN @BeginTransDate AND @EndTransDate AND
 ( ((SELECT COUNT(ID) FROM @LocationIds) = 0) OR (EXISTS (SELECT 1 FROM 
@LocationIds WHERE t.LocationId = ID)) )

LEFT JOIN might be a simpler solution.

SELECT b.*
FROM BLAH b
LEFT JOIN @LocationIds l ON l.ID = b.LocationId

Upvotes: 1

Related Questions