How to write the equivalent Sqlite case statement for query given below?

Question

0.00/5 (No votes)

See more:

This is my working non-sql query:

C#

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 .

SQL

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.

Posted 18-Nov-13 5:32am

Member 8920414

Updated 18-Nov-13 6:17am

v6

Add a Solution

Comments

Zoltán Zörgő 18-Nov-13 11:42am

Be aware, that empty string is not null. An empty textbox is an empty string rather than sql null. I suggest you check for empty values too, or if your provider supports it, set empty string to null conversion.

Member 8920414 18-Nov-13 12:00pm

I have also tried this and got same result
SELECT Cust_Id, Clients_Title, Card_Number, Key_Person, Address_Current, Phone_Number, Mobile_Number, AreaLocation
FROM Customer_New
WHERE
(@Clients_Title = '' OR Clients_Title LIKE '%'+@Clients_Title+'%') AND
(@Address_Current = '' OR Address_Current LIKE '%'+@Address_Current+'%') AND
(@Phone_Number = '' OR Phone_Number LIKE '%'+@Phone_Number+'%') AND
(@Mobile_Number = '' OR Mobile_Number LIKE '%'+@Mobile_Number+'%') AND
(@AreaLocation = '' OR AreaLocation LIKE '%'+@AreaLocation+'%')

Zoltán Zörgő 18-Nov-13 12:07pm

What development platform are you using, what sqlite version, which provider?

Member 8920414 18-Nov-13 12:31pm

I am using visual studio 2012 and developing wpf application.I have to write that query in report viewer data-set. so i need only sqlite query C# code cant work here.I think its sqlite version 1.1 add on in mozilla firefox .

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2013-11-18T08:38:00

You can dynamically build query string ;) Have a look here:
Building Dynamic SQL In a Stored Procedure[^]
Execute Dynamic SQL commands in SQL Server[^]
SQL SERVER – Executing Dynamic SQL – SQL Queries 2012 Joes 2 Pros Volume 4 – Query Programming Objects for SQL Server 2012[^]
IF...ELSE (T-SQL)[^]
BEGIN...END (T-SQL)[^]
EXECUTE (T-SQL)[^]

Example:

SQL

DECLARE @sqlstring NVARCHAR(MAX)
DECLARE @condition NVARCHAR(500)

SET @sqlstring = N'SELECT .... FROM ...'
SET @condition = ' WHERE ... '

IF @title <>'' 
   SET @condition = @condition + ' ClientTitle = ''' + @title + ''''
--etc.