如何为下面给出的查询编写等效的Sqlite case语句？ [英] How to write the equivalent Sqlite case statement for query given below?

问题描述

这是我工作的非SQL查询：

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 { }

在此代码中，where子句的工作是：

1 - 如果所有文本框都为空，则选择所有记录表示跳过where where子句

2-如果在任何1个文本框中输入了一些文本，那么在'where'子句中的部分是在该特定文本框上进行的

3 - 如果在任何多个文本框中输入了一些文本，那么在'where'中的部分通过在它们之间实现AND条件来根据它们制作子句。它意味着输入到文本框中的所有值必须与相应的行属性匹配

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)

此查询中的问题是：

如果文本框中没有提供任何内容，它会搜索NULL或''（空字符串）。我想在上面的代码中如果在文本框中没有输入任何内容，则从where子句跳过属性并且只提供值文本框被考虑用于检查where子句。可以在这种情况下使用sql case和if else条件帮助吗？谁能告诉我如何才能做到这一点？谢谢

注意：在sqlite存储过程中不要退出。所以指导我根据场景编写正确的sql查询。

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.

推荐答案

你可以动态构建查询字符串;）看看这里：

在存储过程中构建动态SQL [ ^ ]

在SQL Server中执行动态SQL命令 [ ^ ]

SQL SERVER - 执行动态SQL - SQL查询2012年第2卷第4卷 - 查询SQL Server 2012的编程对象 [ ^ ]

如果...... ELSE（T-SQL） [ ^ ]

BEGIN ... END（T-SQL） [ ^ ]

EXECUTE（T-SQL） [ ^ ]



Ë例如：

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:

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

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

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

这篇关于如何为下面给出的查询编写等效的Sqlite case语句？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！