I have a stored procedure that uses dynamic SQL to build a paramatized query based on varied input parameters. Rather than use a wildcard search on either sides of the field 'accountnumber' (as it would render the indexes useless) I am thinking of allowing the user to enter an * when they wish to perform such a search. Therefore, if an * is not contained in the string, then I can build a query that will make best use of indexes. It will look something like this:IF @accountNumber IS NOT NULL BEGIN IF charindex('*',@accountNumber) = 0 BEGIN SET @sql = 'SELECT * FROM Account WHERE (AccountNumber = ''' + @accountNumber + ''')' END ELSE IF charindex('*',@accountNumber) = 1 BEGIN SET @sql = 'SELECT * FROM Account WHERE (AccountNumber LIKE ''' + '%' + REPLACE(@accountNumber,'*','') + ''')' END ELSE BEGIN SET @sql = 'SELECT * FROM Account WHERE (AccountNumber LIKE ''' + REPLACE(@accountNumber,'*','') + '%' + ''')' END ENDAre there any better ways to achieve the same result, or different more effective methods?ThankyouHearty head pats
Creating wildcard searches
Posted - 2006-02-21 : 11:47:25
I shall have a read of that this morning. Its amazing how different ways there are to approach such a query. But its all part of the learning curve.Hearty head pats