Click here to Skip to main content
15,072,046 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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)
Posted

1 solution

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

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900