搜索数据库表中的多个列 [英] Search across multiple columns in a db table

问题描述

我需要能够在同一张表中搜索多达7列.我正在尝试创建一个存储的proc以便在VB应用程序中使用，该应用程序将接受一个或多个搜索条件.当然，这将需要即时生成所需的sql.以下是我到目前为止提出的代码.任何帮助将不胜感激.

谢谢

Hi,

I need to be able to search across up to seven columns in the same table. I am trying to create a stored proc for use in a VB app that will accept one or more search criteria. This would of course need to generate the required sql on the fly.Below is the code I have come up with so far. Any help would be much appreciated.

Thanks

CREATE PROCEDURE usp_OuterSearch 
@FreeStock VARCHAR(99), @OuterType VARCHAR(99),
@IntLength VARCHAR(99), @IntWidth VARCHAR(99),
@IntHeight VARCHAR(99), @OuterCap VARCHAR(99),
@PanelSize VARCHAR (99)
AS
DECLARE @SearchArg VARCHAR(999)
DECLARE @SQLString VARCHAR(999)
SET @FreeStock = LTRIM(RTRIM(@FreeStock))
SET @OuterType = LTRIM(RTRIM(@OuterType))
SET @IntLength = LTRIM(RTRIM(@IntLength))
SET @IntWidth = LTRIM(RTRIM(@IntWidth))
SET @IntHeight = LTRIM(RTRIM(@IntHeight))
SET @OuterCap = LTRIM(RTRIM(@OuterCap))
SET @PanelSize = LTRIM(RTRIM(@PanelSize))
SET @SearchArg = "WHERE "
IF LEN(@FreeStock) > 0
	SET @SearchArg = @SearchArg + "FreeStock LIKE ''%" + @FreeStock + "%'' AND "
IF LEN(@OuterType) > 0
	SET @SearchArg = @SearchArg + "OuterType LIKE ''%'' + @OuterType + ''%'' AND "
IF LEN(@IntLength) > 0
	SET @SearchArg = @SearchArg + "IntLength LIKE ''%'' + @IntLength + ''%'' AND "
IF LEN(@IntWidth) > 0
	SET @SearchArg  = @SearchArg + "IntWidth LIKE ''%" + @IntWidth + "%'' AND "
IF LEN(@IntHeight) > 0
	SET @SearchArg = @SearchArg + "IntHeight LIKE ''%" + @IntHeight + "%'' AND "
IF LEN(@OuterCap) > 0
	SET @SearchArg = @SearchArg + "OuterCap LIKE ''%" + @OuterCap + "%'' AND "
IF LEN(@PanelSize) > 0
	SET @SearchArg = @SearchArg + "PanelSize LIKE ''%" + @PanelSize + "%'' AND "
SET @SearchArg = SUBSTRING(@SearchArg, 1, LEN(@SearchArg)-4)
IF LEN(@SearchArg)= 6
	SET @SQLString = "SELECT * FROM Baskets"
ELSE
SET @SQLString = "SELECT * FROM Baskets  + @SearchArg"
PRINT @SQLString
EXECUTE (@SQLString)

推荐答案

好吧，您应该看看

Well, you should have a look at Full Text Search[^] feature for SQL Server.

这篇关于搜索数据库表中的多个列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！