Home Forums SQL Server 7,2000 T-SQL Doing Short Circuiting in WHERE clause RE: Doing Short Circuiting in WHERE clause

  • If @FirstName and @LastName are the variables and contain NULL for 'Not Supplied' then

    WHERE (@FirstName IS NULL OR @FirstName = FirstName)

    AND (@LastName IS NULL OR @LastName = LastName)

    or if you want to test in sequence then

    WHERE (CASE

    WHEN @FirstName IS NOT NULL AND @LastName IS NOT NULL AND @FirstName = FirstName AND @LastName = LastName THEN 1

    WHEN @FirstName IS NOT NULL AND @FirstName = FirstName THEN 1

    WHEN @LastName IS NOT NULL AND @LastName = LastName THEN 1

    ELSE 0

    END) = 1

    Far away is close at hand in the images of elsewhere.
    Anon.