The Temp Table blues OR The Dynamic SQL blues

SQL Apprentice Question
I need some suggestions (if any) for improvement. If you think
I'm on the right track, let me know as well. :) Basically I'm a web dev
creating a page that will search through almost 5 million records, so
speed is important. The users will be able to search via 15 different
criteria. Due to this potential, dynamically building the query string
is the only sensible solution to me. I built my page with a d-sql SP in
the backend and all is well. Then my boss throws me a curve ball. He
wants users to be able to dump in multiple "equipment" numbers into one
of the text boxes, separated by commas, and have the page search via
that as well.

My solution (with the other 14 search criteria's screened out) is
below. Is there any way to improve this method? I can't use a table
variable because it's inaccessible when the d-sql is executed. Thanks.


CREATE PROCEDURE itv_history
@equipment nvarchar(250)
AS
SET DATEFORMAT mdy


CREATE TABLE #ContTable
(
ContValue nvarchar(15)
)


DECLARE @sql nvarchar(2000)
SET @sql = N'SELECT * FROM dbo.ITVD '


if @equipment <> ''
BEGIN


declare @separator nvarchar(1)
set @separator = ','
declare @separator_position int
declare @array_value nvarchar(15)
set @equipment = @equipment + ','


while patindex('%,%' , @equipment) <> 0
begin
select @separator_position = patindex('%,%' , @equipment)
select @array_value = left(@equipment, @separator_position - 1)
Insert #ContTable
Values (ltrim(rtrim(@array_value)))
select @equipment = stuff(@equipment, 1, @separator_position, '')
end


SET @sql = @sql + ' fvd INNER JOIN #ContTable a ON fvd.equipment LIKE
'+'''%'''+'+a.ContValue+'+'''%''')


END


execute (@sql)



Celko Answers

>> My solution (with the other 14 search criteria's screened out) is below. Is there any way to improve this method? <<


Another option is to use the fact that T-SQL can handle over 1000
parameters. This is almost certainly large enough -- heck, 100 is
probalby more typing than anyone wants to do! The code is easy to
write with "cut & paste" editing. Now, if you are lucky, parameter
sniffing might help execution.

SELECT .. FROM .. WHERE parm IN (@p001, @p002,.. , @p100)


Trying to write even a simple parser in SQL is not a good idea-- that
is a good job for 'C' or assembly languages. Is a pair of commas a
zero or a NULL? What do you do with alphas in the string? What about
the stray single quote? Minus signs in the front or the back of a
substring? Decimals -- error, rounded or accepted? Ditto FLOAT and
REAL? Did we squeeze out spaces or not?