编写一个可怕的 SQL 搜索查询 [英] Writing a dreaded SQL search query

问题描述

我正在处理一个似乎不起作用的搜索查询.完整的查询是:

I am working on a search query that does not seem to work. The complete query is:

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 = NULL
    ,@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] '

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

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

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

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

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

当我将参数传递给查询时，如果 Vendor_Num 值为空，则 PRINT(@SQLstr) 的输出为

When I pass paramters to the query, and if the Vendor_Num value is blank, the output from PRINT(@SQLstr) is

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 Item_Num LIKE '2200%' 
**AND Vendor_Num = ''** AND RecAddUserID = 2 AND (RecDate BETWEEN '01/01/2001' 
AND '12/12/2002')

我想要的输出是:

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 Item_Num LIKE '2200%' 
AND RecAddUserID = 2 AND (RecDate BETWEEN '01/01/2001' AND '12/12/2002')

如何构造查询，以便在参数值为空时不显示该字段.

How can I structure the query so if the param value is blank, it does not show the field.

推荐答案

尝试改变

IF (@Vendor_Num IS NOT NULL) 

到

IF (@Vendor_Num IS NOT NULL and LTRIM(@Vendor_Num) <> '') 

这篇关于编写一个可怕的 SQL 搜索查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！