[RESOLVED] [SQL 2000] Conditional Conditions in SP

[RobDog888]

I have a sp that filters records in my datagrid (ASP.NET) but the optional parameters are throwing me off as the user can select any combination of date range only, date range and status, date range and scope, date range and status and scope, status and scope, status only, scope only.

How can I best create the dynamic where clause for this. This is what I have so far.

Thanks.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROCEDURE [dbo].[usp_LoansFilter] AS 
    @From datetime = Null,
    @To datetime = Null,
    @Status nvarchar (15) = Null,
    @Scope nvarchar (15) = Null
AS
BEGIN
    SET NOCOUNT ON;
    SELECT
        [tblProspects].ProspectID,
        [tblProspects].FirstName + ', ' + [tblProspects].LastName AS [Prospect], 'For display purposes only
        [tblLoans].LoanID,
        [tblLoans].Status,
        [tblLoans].DateSubmitted,
        [tblLoans].PriceComplete,
        [tblLoans].FMA,
        [tblLoans].JFMA,
        [tblLoans].Purpose,
        [tblLoans].LoanAmount,
        [tblLoans].Created,
        [tblLoans].Last_Updated
    FROM
        tblLoans 
    INNER JOIN
        tblProspects 
        ON tblLoans.ProspectID = tblProspects.ProspectID 
    WHERE
        (([tblLoans].Created >= @From) AND ([tblLoans].Created <= @To))
            AND
        (([tblLoans].Last_Updated <= @From) AND ([tblLoans].Last_Updated <= @To))
            AND
        (([tblLoans].Status = @Status))
            AND
        (([tblLoans].PriceComplete = @Scope))
END
GO