locked
How this pagination code is working RRS feed

  • Question

  • User264732274 posted

    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

    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

    Wednesday, December 16, 2015 3:16 PM

Answers

  • User-821857111 posted

    The piece of code that you posted caters for optional search criteria. I assume that there is a form that allows the user to input values for contactid, firstname, lastname, emailaddress etc. It will allow them to search the database using any or all of these fields as a filter. If the user doesn't enter anything for any of these fields, the associated parameter value will be NULL, and the first part of the condition will be true and the second part of the condition will not be processed. If there is a value, the first part of the condition will be false, and the second part of the condition will be processed.

    If none of the fields have data, the resulting condition will effectively read ".. WHERE true" and all the records will be returned.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 17, 2015 7:21 AM
  • User-1716253493 posted
    WHERE (@param is null or colname=@param) AND ...

    This mean,

    if you pass @param with null (i.e blank textbox), (@param is null or colname=@param) become true

    if you pass @param with a value the the data filtered by colname=the value

    (@param is null or colname=@param) will be ignored (show all records) if the @param value is null

    So, you need AND operator to filter the data with another column

    @param is null mean show all records when the textbox is empty

    colname=@param show only macthing records (in this moment the textbox is not empty, @param is not null)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 17, 2015 7:32 AM

All replies

  • User-821857111 posted

    The piece of code that you posted caters for optional search criteria. I assume that there is a form that allows the user to input values for contactid, firstname, lastname, emailaddress etc. It will allow them to search the database using any or all of these fields as a filter. If the user doesn't enter anything for any of these fields, the associated parameter value will be NULL, and the first part of the condition will be true and the second part of the condition will not be processed. If there is a value, the first part of the condition will be false, and the second part of the condition will be processed.

    If none of the fields have data, the resulting condition will effectively read ".. WHERE true" and all the records will be returned.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 17, 2015 7:21 AM
  • User-1716253493 posted
    WHERE (@param is null or colname=@param) AND ...

    This mean,

    if you pass @param with null (i.e blank textbox), (@param is null or colname=@param) become true

    if you pass @param with a value the the data filtered by colname=the value

    (@param is null or colname=@param) will be ignored (show all records) if the @param value is null

    So, you need AND operator to filter the data with another column

    @param is null mean show all records when the textbox is empty

    colname=@param show only macthing records (in this moment the textbox is not empty, @param is not null)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 17, 2015 7:32 AM