编写一个可怕的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) 

当我通过所有空的参数值，我得到一个错误：

"Failed to convert parameter value from a String to a 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

你不太建设字符串正确，据我可以告诉。如果没有传入@Item_Num，那么最终将没有WHERE关键字......您将只有“FROM [tblItem_Log] AND ...”

我会使所有条件附加为“而且......”，并为您最初的声明中使用：

FROM [tbl_Item_Log] WHERE (1=1) 

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

我也只是注意到，如果你不通过日期值，你会最终执行一个NULL字符串，因为你最终的连接将最终导致NULL。如果您打算使用动态SQL来构建查询，那么这些是您需要非常关注的事情。

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

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

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

，而不是你有看似不必要的子查询。

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

Answer 2

IF (Search_User_ID IS NOT NULL) 

需要一个@符号盈变量

你说你正在传递空字符串的所有变量，但一个是int型的，它不能把未int类型数据的空字符串。不敢相信我没有注意到，那是第一次。

Answer 3

你为什么不使用单一的参数化查询是这样的：

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 4

这里确实有几个不同的存储过程。为什么不把它们分开写呢？所有由switch语句控制的内容都可以轻易放在程序代码中。 LTRIM呼叫也一样。

你可以使用switch语句从一个SP中调用它们;但我认为最好不要将它们合并在一起。 SP查询将更容易优化，代码将被简化。巩固他们并没有太多的收获。

的不知道您的业务规则，但你可以简化与

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

此之外还有SQL看起来你对其中项目数量提供或不提供单独的情况下的逻辑。其他领域也一样。你的每个用例看起来像解析为一个非常简单的查询。