locked
Search multiple columns stored procedure RRS feed

  • Question

  • User-259260637 posted

    I am trying to get results from a query where the columns can be false or null. I have looked online and trying this code but it doesn't seem to be the code I need:

    I want to be able to search anything if it is null or not.

    Can anybody tell me where i am going wrong please.

    @TownName nvarchar(200) = '',
    	@var1 bit = null,
    	@var2 bit = null,
    	@var3 bit = null,
    	@var4 bit = null,
    	@var5 bit = null,
    	@var6 bit = null,
    	@var7 bit = null
    AS
    BEGIN
    SELECT TOP(50) NEWID() ....
    FROM dbo.tblMembers m 
    WHERE RTRIM(REPLACE(m.Town, '-',' ')) = RTRIM(@TownName) AND m.ShowInSearch = 1 AND m.RegistrationStatus = 'Live' 
    	and (m.Tr_p = ISNULL(@var1, m.Tr_p)) 
    	and (m.Tr_el = ISNULL(@var2, m.Tr_el))
    	and (m.Tr_he = ISNULL(@var3,m.Tr_he)) 
    	and (m.Tr_mi = ISNULL(@var4, m.Tr_mi)) 
    	and (m.Tr_ve = ISNULL(@var5, m.Tr_ve)) 
    	and (m.Tr_gr1 = ISNULL(@var6, m.Tr_gr1)) 
    	and (m.Tr_gr2 = ISNULL(@var7, m.Tr_gr2))
    ORDER BY randid asc
    END

    Monday, April 20, 2015 8:35 AM

Answers

  • User-821857111 posted
    and (@var1 IS NULL OR m.Tr_p = @var1)
    and (@var2 IS NULL OR m.Tr_el = @var2)
    and (@var3 IS NULL OR m.Tr_he = @var3) 
    and (@var4 IS NULL OR m.Tr_mi = @var4) //etc
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 20, 2015 9:08 AM