This is my working non-sql query:
Query = "select Cust_Id,Card_Number,Clients_Title,Address_Current,Phone_Number,Mobile_Number from Customer_New Where 1=1";
try
{
if (txt_title.Text != "")
Query += " and Clients_Title Like '%" + txt_title.Text + "%'";
if (txt_address.Text != "")
Query += " and Address_Current Like '%" + txt_address.Text + "%'";
if (txt_phone.Text != "")
Query += " and Phone_Number Like '%" + txt_phone.Text + "%'";
if (txt_mobile.Text != "")
Query += " and Mobile_Number Like '%" + txt_mobile.Text + "%'";
if (cbo_location.Text != "")
Query += " and AreaLocation Like '%" + cbo_location.Text + "%'";
}
catch { }
In this code the working of "where" clause is:
1-If all text boxes are null it selects all the records mean skip where clause
2-if some text is entered in any 1 text box then section in 'where' clause is made on that particular text box
3-if some text is entered in any multiple text box then section in 'where' clause is made according to them by fulfilling "AND" condition between them .It mean all values entered into text boxes must match with corresponding rows attributes
Here I am attempting to write its equivalent SQL statement .
SELECT Cust_Id, Clients_Title, Card_Number, Key_Person, Address_Current, Phone_Number, Mobile_Number, AreaLocation
FROM Customer_New
WHERE (@Clients_Title IS NULL OR Clients_Title = @Clients_Title )
AND (@Address_Current IS NULL OR Address_Current = @Address_Current )
AND (@Phone_Number IS NULL OR Phone_Number = @Phone_Number)
AND (@Mobile_Number IS NULL OR Mobile_Number = @Mobile_Number )
AND (@AreaLocation IS NULL OR AreaLocation = @AreaLocation)
Problem in this query is :
It search NULL or ''(empty string) if nothing is provided in text box.I want as in above code if nothing entered in text box that attribute get skip from where clause and only values provided in text box are considered for checking in where clause.Can sql case and if else condition help in this scenario? Can anyone tell me how I can accomplish this ? Thanks
Note : In sqlite stored procedure don't exit .So guide me to write the correct sql query according to scenario.