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))
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.