Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

i'm trying to create a procedure with filters for search.

If the parameter is "NULL" the value of parameter will be column value;

Here my problem: When my parameter and column value is NULL.

I have no results because of this.

I know that query: "COLUMN_NAME IS NULL" return the result even if the value is NULL, but i don't know how to implement this.

I trying to do this with "CASE" but without success.

CREATE PROCEDURE SEARCH_QUERYS
        @ID   int,
        @LIVE bit,
        @DATA datetime
AS
BEGIN
        SELECT * FROM TB_SEARCH
        WHERE 
        ID = ISNULL(@ID,ID) AND
        LIVE = ISNULL(@LIVE,LIVE) AND
        DATA = ISNULL(@DATA,DATA)
END
share|improve this question
    
It is generally not a good practice (INMO) to allow nulls in a column named ID. –  Emmad Kareem Oct 11 '13 at 22:24
add comment

1 Answer

up vote 3 down vote accepted
ALTER PROCEDURE dbo.SEARCH_QUERYS -- always use schema prefix!
        @ID   int,
        @LIVE bit,
        @DATA datetime
AS
BEGIN
        SELECT ID, LIVE, DATA --, ... don't use SELECT *!
        FROM dbo.TB_SEARCH -- always use schema prefix!
        WHERE 
        (ID = @ID OR @ID IS NULL)
        AND (LIVE = @LIVE OR @LIVE IS NULL)
        AND (DATA = @DATA OR @DATA IS NULL); -- always use semi-colons!
END
GO

In some cases though you are better off building the query dynamically.

Refs:

share|improve this answer
    
Aaron, this query is exactly what i tried before! Don't work. Note: Column "DATA" default value is NULL. also: What is difference between my query and yours? –  user29301 Oct 12 '13 at 2:12
    
@Laerte does "Don't work" mean you tried it before and it didn't work, but now it does, or that this doesn't work now? –  Aaron Bertrand Oct 12 '13 at 2:17
    
I tried right now and still don't work. –  user29301 Oct 12 '13 at 2:18
    
Also: What is difference between my query and yours? –  user29301 Oct 12 '13 at 2:19
    
@Laerte your query says ISNULL(@param, column) = column. This comparison does not work if the column is NULL because NULL = NULL is unknown, not true. In a WHERE clause this means reject the row. My query says "if the parameter is NULL, return the row; if the parameter IS NOT NULL, return the row only if the column matches." Did you try this logic on all three of your columns/parameters, or just the ID column? Can you show some sample data that demonstrates how this does not work? –  Aaron Bertrand Oct 12 '13 at 2:25
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.