How this pagination code would be working

Question

i just read a article on pagination of records in sql server from this link http://www.codeproject.com/Articles/590341/Stored-Procedure-with-Sorting-Paging-and-Filtering

http://sqlwithmanoj.com/2011/12/30/creating-stored-procedures-with-dynamic-search-paging-pagination/

please see their pagination script and specially look at the where area

 WHERE
    (@lContactID IS NULL OR ContactID = @lContactID)
    AND(@lFirstName IS NULL OR FirstName LIKE ‘%’ + @lFirstName + ‘%’)
    AND(@lLastName IS NULL OR LastName LIKE ‘%’ + @lLastName + ‘%’)
    AND (@lEmailAddress IS NULL OR EmailAddress LIKE ‘%’ + @lEmailAddress + ‘%’)
    AND (@lEmailPromotion IS NULL OR EmailPromotion = @lEmailPromotion)
    AND (@lPhone IS NULL OR Phone  LIKE ‘%’ +@lPhone+ ‘%’)

how the above where clause will work ?

they use AND clause instead of OR.

they wrote like if contactid is null or ContactID = @lContactID and again they use LastName IS NULL OR LastName LIKE ‘%’ + @lLastName + ‘%’

anyone can help me to understand how their where clause will work when user send no value for those fields in where clause or may be user will send multiple value for different fields in where clause.

please help. thanks

Answer 1

First of all: You should choose a better subject, cause pagination is not the real topic.

Then 'no value' is not a term we should use. Say NULL or empty string or value.

And for your question: It's a simple catch-all (the inner OR) condition. Where the different search criteria are additive. So when you search for first and last name, that only rows are returned where both single conditions are true.

Answer 2

Hi

If you take a closer look at store proc it uses commontableExpress and also rownumber function of sql.

Rownumber function is used in table expression which gives a result set with record no and based on this record set and based on parameters like pagesize n page no table get filtered futher

Ref

Common table expression from sql authority

Row_number

If you find this answer helpful, Kindly marked as answer

Thanking you,

Shridhar J Joshi

---

Shridhar J Joshi Thanks a lot

Answer 3

Stefan explained correctly how the logic works. You may also want to take a look at the following blog post

Do you use Column=@Param OR @Param IS NULL in your WHERE clause? Don’t, it doesn’t perform

and also Gail Shaw's blog post

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

The solution for SQL Server 2008 R2 and up will be to add OPTION (RECOMPILE) at the end of the query. 

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles