编写可怕的 SQL 搜索查询（第二阶段）

Question

我正在研究一个搜索查询（使用 asp.net 3.5 前端），这看起来很简单，但相当复杂。 完整的查询是：

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[usp_Item_Search]
    @Item_Num varchar(30) = NULL
    ,@Search_Type int = NULL
    ,@Vendor_Num varchar(10) = NULL
    ,@Search_User_ID int = 0
    ,@StartDate smalldatetime = NULL
    ,@EndDate smalldatetime = NULL
AS
DECLARE @SQLstr as nvarchar(4000)

Set @SQLstr = 'SELECT RecID, Vendor_Num, Vendor_Name, InvoiceNum, Item_Num, 
(SELECT CONVERT(VARCHAR(11), RecDate, 106) AS [DD MON YYYY]) As RecDate, NeedsUpdate, RecAddUserID FROM [tbl_ItemLog] where 1=1 '

IF (@Item_Num IS NOT NULL and LTRIM(@Item_Num) <> '')
    Begin
        If @Search_Type = 0
            BEGIN
                Set @SQLstr = @SQLstr +  'AND Item_Num LIKE ''' + @Item_Num + '%'''
            END
        If @Search_Type = 1
            BEGIN
                Set @SQLstr = @SQLstr + 'AND Item_Num LIKE ''%' + @Item_Num + '%'''
            END
        If @Search_Type = 2
            BEGIN
                Set @SQLstr = @SQLstr + 'AND Item_Num LIKE ''%' + @Item_Num + ''''
            END
    End

IF (@Vendor_Num IS NOT NULL and LTRIM(@Vendor_Num) <> '')
    Begin
        Set @SQLstr = @SQLstr + ' AND Vendor_Num = ''' + @Vendor_Num + ''''
    End

IF (@Search_User_ID IS NOT NULL and @Search_User_ID > 0)
    Begin
        Set @SQLstr = @SQLstr + ' AND RecAddUserID = ' + convert(nvarchar(20),@Search_User_ID)
    End

Set @SQLstr = @SQLstr + ' AND (RecDate BETWEEN ''' + convert(nvarchar(10),@StartDate,106) + ''' AND ''' + convert(nvarchar(10),@EndDate,106) + ''')'

PRINT (@SQLstr)
--Execute (@SQLstr)

当我传递所有空参数值时，出现错误：

“参数值转换失败 从 String 到 Int32。”

调用存储过程的 asp.net 代码是：

        //Display search results in GridView;
        SqlConnection con = new SqlConnection(strConn);
        //string sqlItemSearch = "usp_Item_Search";
        SqlCommand cmdItemSearch = new SqlCommand(sqlItemSearch, con);
        cmdItemSearch.CommandType = CommandType.StoredProcedure;

        cmdItemSearch.Parameters.Add(new SqlParameter("@Item_Num", SqlDbType.VarChar, 30));
        cmdItemSearch.Parameters["@Item_Num"].Value = txtItemNumber.Text.Trim();

        cmdItemSearch.Parameters.Add(new SqlParameter("@Search_Type", SqlDbType.Int));
        cmdItemSearch.Parameters["@Search_Type"].Value = ddlSearchType.SelectedItem.Value;

        cmdItemSearch.Parameters.Add(new SqlParameter("@Vendor_Num", SqlDbType.VarChar, 10));
        cmdItemSearch.Parameters["@Vendor_Num"].Value = txtVendorNumber.Text.Trim();

        cmdItemSearch.Parameters.Add(new SqlParameter("@Search_User_ID", SqlDbType.Int));
        cmdItemSearch.Parameters["@Search_User_ID"].Value = ddlSeachUser.SelectedItem.Value;

        if (!string.IsNullOrEmpty(txtStartDate.Text))
        {
            cmdItemSearch.Parameters.Add(new SqlParameter("@StartDate", SqlDbType.DateTime));
            cmdItemSearch.Parameters["@StartDate"].Value = Convert.ToDateTime(txtStartDate.Text.Trim());
        }
        else
        {
            cmdItemSearch.Parameters.Add(new SqlParameter("@StartDate", SqlDbType.DateTime));
            cmdItemSearch.Parameters["@StartDate"].Value = Convert.ToDateTime("01/01/1996");
        }

        if (!string.IsNullOrEmpty(txtEndDate.Text))
        {
            cmdItemSearch.Parameters.Add(new SqlParameter("@EndDate", SqlDbType.DateTime));
            cmdItemSearch.Parameters["@EndDate"].Value = Convert.ToDateTime(txtEndDate.Text.Trim());
        }
        else
        {
            cmdItemSearch.Parameters.Add(new SqlParameter("@EndDate", SqlDbType.DateTime));
            cmdItemSearch.Parameters["@EndDate"].Value = Convert.ToDateTime(DateTime.Now);
        }
        con.Open();

        SqlDataAdapter ada = new SqlDataAdapter(cmdItemSearch);
        DataSet ds = new DataSet();
        ada.Fill(ds);

            gvSearchDetailResults.DataSource = ds;
            gvSearchDetailResults.DataBind();
            pnlSearchResults.Visible = true;

我怎样才能解决这个问题？

Answer 1

为什么不使用像这样的单个参数化查询：

select 
   recdid,
   Vendor_Num,
   Vendor_Name,
   InvoiceNum,
   Item_Num, 
   CONVERT(VARCHAR(11), RecDate, 106) as RecDate,
   NeedsUpdate, 
   RecAddUserID 
FROM 
  [tbl_ItemLog] as t
where
   (((Item_num like @Item_Num + '%' and @Search_Type = 0) OR
    (Item_num like '%' + @Item_Num + '%' and @Search_Type =  1) OR
    (Item_num like '%' + @Item_Num + '%' and @Search_Type = 2))  
        OR
    @Item_Num IS NULL) AND
   (Vendor_Num = @Vendor_Num OR @Vendor_Num IS NULL) AND
   (RecAddUserId = @Search_User_Id OR @Search_User_Id IS NULL) AND
   (RecDate BETWEEN @StartDate AND @EndDate)

Answer 2

这里确实有几个不同的存储过程。为什么不把它们分开写呢？由 switch 语句控制的所有内容都可以轻松地用过程代码表示。 LTRIM 调用也是如此。

您可以使用 switch 语句从单个 SP 调用它们；但我认为通常最好一开始就不要合并它们。 SP查询将更容易优化，并且代码将被简化。合并它们并没有多大好处。

不确定您的业务规则，但您可以使用以下命令来简化此外部 SQL

switch(search_type) {    
case 1:  
    do_query_type_1(args);  
    break;  
case 2:  
    do_query_type_2(args);  
    break;  
case 3:  
    do_query_type_3(args);  
    break;  
default:  
    whatever ...  
}

另外，对于提供或不提供商品编号的情况，您似乎有单独的逻辑。其他领域也一样。您的每个用例看起来都解析为一个非常简单的查询。

Answer 3

IF (Search_User_ID IS NOT NULL) 

变量前面需要一个 @ 符号

您说您要为所有变量传递空字符串，但其中一个是 int，它不能接受不是 int 数据的空字符串。不敢相信我第一次没有注意到这一点。

Answer 4

据我所知，您没有完全正确地构建字符串。如果没有传入 @Item_Num，您最终将没有 WHERE 关键字...您将只有“FROM [tblItem_Log] AND...”

我会将所有条件附加为“AND ... " 并且作为您的初始语句使用：

FROM [tbl_Item_Log] WHERE (1=1)

既然您有代码来返回生成的字符串，为什么不将其放入 SSMS 并尝试运行它呢？

我还注意到，如果您不传入日期值，您最终将执行 NULL 字符串，因为您的最终连接最终将导致 NULL。如果您要使用动态 SQL 来构建查询，那么您需要密切关注这些事情。

一旦我更正，我就能够运行存储过程而没有任何错误（至少生成看起来像有效的 SQL 语句）。这让我相信这可能是基础表中的数据类型的问题。你能提供它的定义吗？

最后一点：就我个人而言，我会使用

CONVERT(VARCHAR(11), RecDate, 106) AS RecDate

而不是您拥有的看似不必要的子查询。

另一个编辑：
您可能需要删除检查 LTRIM(@Search_User_ID) <> 的代码”。这是一段毫无意义的代码，也许您的服务器/连接的特定设置导致它由于类型不匹配而失败。