CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
MAK
MAK

Reputation: 7260

SQL Server 2008 R2: Prepare Dynamic WHERE Clause

I have the following stored procedure with four parameters.

Stored procedure spTest:

CREATE PROCEDURE spTest
    @Name varchar(20) = '',
    @Address varchar(100) = '',
    @City varchar(50) = '',
    @Pin varchar(50) = ''
AS
    DECLARE @DynamicWhere varchar(max)
    DECLARE @Query varchar(max)

    /* Here I want to prepare a dynamic where clause for all possibilities */
    SET @Query = 'SELECT * FROM Test_Table '+ @DynamicWhere +'';

    EXECUTE(@Query);
GO

Well I am preparing it like this:

IF @Name = '' AND @Address = '' AND @City = '' AND @Pin = '' 
BEGIN
     SET @DynamicWhere = '';
END
ELSE IF @Name != '' AND @Address = '' AND @City = '' AND @Pin = '' 
BEGIN
     SET @DynamicWhere = 'Name ='''+@Name+'''';
END
ELSE IF @Name != '' AND @Address != '' AND @City = '' AND @Pin = '' 
BEGIN
     SET @DynamicWhere = 'Name ='''+@Name+''' AND Address ='''+@Address+'''';
END
......
......
Many possibilities

Is this a right way OR is there any better way to prepare the dynamic WHERE clause?

Upvotes: 0

Views: 226

Answers (3)

Ajay2707
Ajay2707

Reputation: 5798

I update the @Zohar answer. Define blank is bad habits, ideally define with null and later use blank. So the query will be

CREATE PROCEDURE spTest
    @Name varchar(20) = null,
    @Address varchar(100) = null,
    @City varchar(50) = null,
    @Pin varchar(50) = null
AS
    SELECT * 
    FROM Test_Table 
    WHERE (Name = ISNULL(@Name,'') )
    AND (Address = ISNULL(@Address,''))
    AND (City = ISNULL(@City,''))
    AND (Pin = ISNULL(@Pin,''));
GO

Even I like the @Biswa answer as it use current version of sql server, but Sqlserver 2008R2 does not have this function.

Upvotes: 1

Biswabid
Biswabid

Reputation: 1411

You can use ISNULL and NULLIF also in this case:
below code should work :

CREATE PROCEDURE spTest
    @Name varchar(20) = '',
    @Address varchar(100) = '',
    @City varchar(50) = '',
    @Pin varchar(50) = ''
AS
    SET @Name=NULLIF(@Name,'')
    SET @Address=NULLIF(@Address,'')
    SET @City=NULLIF(@City,'')
    SET @Pin=NULLIF(@Pin,'')

    SELECT * 
    FROM Test_Table 
    WHERE Name = ISNULL(@Name,Name)
    AND Address = ISNULL(@Address,Address)
    AND City = ISNULL(@City,City)
    AND Pin = ISNULL(@Pin,Pin)

GO

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82474

It's called catch-all queries and it basically goes like this:

CREATE PROCEDURE spTest
    @Name varchar(20) = '',
    @Address varchar(100) = '',
    @City varchar(50) = '',
    @Pin varchar(50) = ''
AS
    SELECT * 
    FROM Test_Table 
    WHERE (@Name = '' OR Name = @Name)
    AND (@Address = '' OR Address = @Address)
    AND (@City = '' OR City = @City)
    AND (@Pin = '' OR Pin = @Pin);
GO

You also might want to read this article about catch all queries

Upvotes: 5

Related Questions