Last post Dec 17, 2015 07:32 AM by oned_gk
Dec 16, 2015 03:16 PM|sudip_inn|LINK
i just read a article on pagination of records in sql server from this link
please see their pagination script and specially look at the where area
(@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
Dec 17, 2015 07:21 AM|Mikesdotnetting|LINK
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.
Dec 17, 2015 07:32 AM|oned_gk|LINK
WHERE (@param is null or colname=@param) AND ...
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
(@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
colname=@param show only macthing records (in this moment the textbox is not empty, @param is not null)