Search across multiple columns in a db table

See more:

SQL-Server-2005

SQL-Server

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)