Default query for all implementations when query parameters are empty (null) in SQL Server

Recently encountered a more interesting problem in the project, the online search for some methods, summarized here to share.

We often encounter such a scenario: the need to query data, there are some query conditions, but when the query, we hope that when a condition is empty, then do not filter this condition, the main idea is to deal with the following:

1, the program collects query query condition processing, this suitable for dynamic SQL, splicing SQL when the parameters are empty to decide whether to splice the corresponding query conditions.

2, SQL processing, this situation is suitable for stored procedures, fixed parameters of the scene, or non-splicing where conditions of the scene, the stored procedure is generally not used in the way of splicing SQL, then how to implement the parameter is NULL when the automatic query all the data? This article is to discuss this issue.

For example, there is a table called book, recorded some books of information, the table is relatively simple, the next can be seen, if the page has a query criteria, according to the publisher to find books, we generally use the way (the definition of parameters and data collection on the Assignment simulation page):

1 DECLARE @publishers VARCHAR (a); 2 SET @publishers = ' Tsinghua University Press ' ; 3 SELECT *  from WHERE Publishers=@publishers

The results are as follows:

When the publisher filter condition is NULL, how do you write SQL?

1 DECLARE @publishers VARCHAR (a); 2 SELECT *  from WHERE Publishers=ISNULL(@publishers, Publishers)

Query Result:

This allows you to achieve the above requirements, especially when stored procedures may be more appropriate.

Default query for all implementations when query parameters are empty (null) in SQL Server