CODEWITHSUNDEEP
sqlsql-serversql-server-2008stored-procedures
Racksickle
Racksickle

Reputation: 53

Optional Multiple Parameter SQL Server 2008 Search returning nothing

I am attempting to create a stored procedure for a multi-parameter search where the each parameter. My code below once executed returns nothing for me, both with and without parameters entered. I feel there is a logic error with my 'WHERE' section that I am just not seeing. Any and all help is greatly appreciated! =]

-Rock

-- =============================================
-- Author:      Rock G Bolton
-- Create date: 1/2/2014
-- Description: Gets Complaint(s) based on search parameters
-- =============================================
Create PROCEDURE [dbo].[GetComplaintBySearch] 
    @Type int = NULL,
    @Location int = NULL,
    @Status int = NULL,
    @Customer nvarchar(50) = NULL,
    @Responisble nvarchar(50) = NULL,
    @StartDateFrom datetime = NULL,
    @StartDateTo datetime = NULL,
    @DueDateFrom datetime = NULL,
    @DueDateTo datetime = NULL

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT  
    [Complaints].[Id],
    [Complaints].[Type_fk],
    [Complaints].[RefNo],
    [Complaints].[Status_fk],
    [Complaints].[Reason_fk],
    [Complaints].[ProductLine_fk],
    [Complaints].[Model],
    [Complaints].[SerialNumber],
    [Complaints].[Description],
    [Complaints].[CallRecdBy],
    [Complaints].[CallRecdDate],
    [Complaints].[Responsible],
    [Complaints].[DueDate],
    [Complaints].[Customer],
    [Complaints].[ContactName],
    [Complaints].[Phone],
    [Complaints].[Email],
    [Complaints].[Source],
    [Complaints].[InitiatedBy],
    [Complaints].[StartDate]
    FROM [dbo].[Complaints] 
    WHERE ([Complaints].[Type_fk] = @Type OR @Type IS NULL)
        AND ([Complaints].[Status_fk] = @Status OR @Status IS NOT NULL)
        AND ([Complaints].[Customer] LIKE @Customer + '%' OR @Customer IS NULL)
        AND ([Complaints].[Responsible] Like @Responisble + '%' OR @Responisble IS NULL)
        AND (([Complaints].[StartDate] >= @StartDateFrom OR @StartDateFrom IS NULL) OR ([Complaints].[CallRecdDate] >= @StartDateFrom OR @StartDateFrom IS NULL))
        AND (([Complaints].[StartDate] <= @StartDateTo OR @StartDateTo IS NULL) OR ([Complaints].[CallRecdDate] <= @StartDateTo OR @StartDateTo IS NULL))
        AND ([Complaints].[DueDate] >= @DueDateFrom OR @DueDateFrom IS NULL)
        AND ([Complaints].[DueDate] <= @DueDateTo OR @DueDateTo IS NULL)

END

Upvotes: 0

Views: 741

Answers (1)

Solomon Rutzky
Solomon Rutzky

Reputation: 48836

AND ([Complaints].[Status_fk] = @Status OR @Status IS NOT NULL)

should be:

AND ([Complaints].[Status_fk] = @Status OR @Status IS NULL)

just like the rest of the conditions. You seem to have an extraneous NOT in there.

Upvotes: 3

Related Questions