Problems using case statement in where clause with table valued parameters

Question

question

Jon 2 asked Feb 11, '10

Problems using case statement in where clause with table valued parameters

Hello, this is my first post here. I'm quite new to SQL Server and stored procedures, so please forgive me if the answer is obvious. I have a requirement to make a stored procedure very flexible in it's inputs it can take as it will be used to search an EventLogs table in our database. Some of the inputs are table valued parameters, which I'm also new to. I'm basing this on another example of a similar stored procedure here at work that didn't use table valued parameters.

I've created a User-Defined Table Type as follows, which is used to pass in lists of integers to the store procedure.

CREATE TYPE IntList AS TABLE (Value int NOT NULL PRIMARY KEY)

My first crack at the stored procedure looked like the following.

ALTER PROCEDURE GetEventLogs ( @EventLogID INT = NULL ,@ComponentIDs IntList READONLY ,@CreatedTimeStart DATETIME = NULL ,@CreatedTimeEnd DATETIME = NULL ,@EventTypeIDs IntList READONLY ,@Username NVARCHAR(50) = NULL ,@FullName NVARCHAR(100) = NULL ,@EventGroupIDs IntList READONLY ) AS BEGIN

SELECT *                    
FROM EventLogs, EventGroups, EventTypes, Components                    
WHERE EventLogs.EventLogID = IsNULL(@EventLogID, EventLogs.EventLogID)	                    
AND EventLogs.CreatedTime >= IsNULL(@CreatedTimeStart, EventLogs.CreatedTime)                    
AND EventLogs.CreatedTime <= IsNULL(@CreatedTimeEnd, EventLogs.CreatedTime)                    
AND EventLogs.Username = IsNULL(@Username, EventLogs.Username)                    
AND EventLogs.FullName = IsNULL(@FullName, EventLogs.FullName)                    
AND EventLogs.EventTypeID = EventTypes.EventTypeID -- Join Tables                    
AND EventLogs.ComponentID = Components.ComponentID -- Join Tables                    
AND EventTypes.EventGroupID = EventGroups.EventGroupID -- Join Tables                    
AND EventLogs.ComponentID IN (IsNULL((SELECT Value FROM @ComponentIDs), EventLogs.ComponentID))	                    
AND EventLogs.EventTypeID IN (IsNULL((SELECT Value FROM @EventTypeIDs), EventLogs.EventTypeID))	                    
AND EventGroups.EventGroupID IN (IsNULL((SELECT Value FROM @EventGroupIDs), EventGroups.EventGroupID))

END

By using IsNull it allows any of the fields to be optional, I'm not sure if there is a better way to do this but this is how I've been asked to do it. The issue with the above stored procedure is that you get the error "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression." when running it. This makes sense since the IsNull function should only take a single input. My next crack at it was to use a case statement, which is new to me.

I tried something like the following but got the same error.

AND EventLogs.EventTypeID IN (CASE WHEN EXISTS(SELECT * FROM @EventTypeIDs) THEN (SELECT Value FROM @EventTypeIDs) ELSE EventLogs.EventTypeID END)

I've also tried many combinations with the case statement and can't figure it out. Is there a way (without resorting to dynamic SQL) that I can do what I'm trying to accomplish.

Thanks for any help.

Jon

case-statement

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-02-12T02:52:05Z

Scot Hauder answered Feb 12, '10

            
ALTER PROCEDURE GetEventLogs (             
     @EventLogID INT = NULL             
    ,@ComponentIDs IntList READONLY             
    ,@CreatedTimeStart DATETIME = NULL             
    ,@CreatedTimeEnd DATETIME = NULL             
    ,@EventTypeIDs IntList READONLY             
    ,@Username NVARCHAR(50) = NULL             
    ,@FullName NVARCHAR(100) = NULL             
    ,@EventGroupIDs IntList READONLY             
    )             
AS BEGIN            
            
DECLARE @ComponentFilter INT            
DECLARE @EventTypeFilter INT            
DECLARE @EventGroupFilter INT            
            
SELECT @ComponentFilter = COUNT(*) FROM @ComponentIDs            
SELECT @EventTypeFilter = COUNT(*) FROM @EventTypeIDs            
SELECT @EventGroupFilter = COUNT(*) FROM @EventGroupIDs            
            
SET NOCOUNT ON            
            
SELECT *            
FROM Components c            
JOIN EventLogs el ON (el.ComponentID = c.ComponentID)            
JOIN EventTypes et ON (et.EventTypeID = el.EventTypeID)            
JOIN EventGroups eg ON (eg.EventGroupID = et.EventGroupID)             
            
WHERE el.EventLogID = IsNULL(@EventLogID, el.EventLogID)              
AND el.CreatedTime >= IsNULL(@CreatedTimeStart, el.CreatedTime)            
AND el.CreatedTime <= IsNULL(@CreatedTimeEnd, el.CreatedTime)            
AND el.Username = IsNULL(@Username, el.Username)            
AND el.FullName = IsNULL(@FullName, el.FullName)            
            
AND (el.ComponentID IN (SELECT Value FROM @ComponentIDs) OR @ComponentFilter = 0)            
            
AND (el.EventTypeID IN (SELECT Value FROM @EventTypeIDs) OR @EventTypeFilter = 0)            
            
AND (eg.EventGroupID IN (SELECT Value FROM @EventGroupIDs) OR @EventGroupFilter = 0)            
END

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question