[thelist] [long] trying to convert VBScript into a stored proc

[Jeremy Weiss]

Okay, more of my woes trying to move everything over to SQL Server.  I've
got the following script on a few different pages in my site and I'd like to
make a stored proc out of it.

'multiple zip codes are passed from previous page in an array
zipcode_array = Split(zipcodes,",")

For Each V in zipcode_array
   If V = " " Then
   Exit For
   End If
'I know, I need to make it Is instead of Like, it's on my to do list,
honest.
    SQL = "SELECT zipcode FROM zip_tbl WHERE zipcode Like '%" & Replace(V,
"'", "''") & "%' "
      Set R = conn.execute(SQL)
      If NOT (R.EOF OR R.BOF) Then
         conn.close
         Response.Redirect "power__search.htm"
         Exit For
      End If
Next


Now, I feel that what I've got is close. the base of it was a script I found
online and then customized it to do what I needed.  But Access throws up an
error every time I try to save it.  (side note, any recommendations on
software to use to interact with the SQL DB instead of Access?)


Create Procedure IsZipAvailable

@zipcode_array
@separator

As

-- @zipcode_array is the array we wish to parse
-- @Separator is the separator character such as a comma

declare @separator_position int -- This is used to locate each separator
character
declare @array_value varchar(1000) -- this holds each array value as it is
returned

-- For my loop to work I need an extra separator at the end.  I always look
to the
-- left of the separator character for each array value
set @zipcode_array = @zipcode_array + @separator

-- Loop through the string searching for separator characters
while patindex('%' + @separator + '%' , @zipcode_array) <> 0
begin

  -- patindex matches the a pattern against a string
  select @separator_position =  patindex('%' + @separator + '%' ,
@zipcode_array)
  select @array_value = left(@zipcode_array, @separator_position - 1)

  -- This is where you process the values passed.
  -- Replace this select statement with your processing
  -- @array_value holds the value of this element of the array
  select Array_Value = @array_value
  set @array_value = LTRIM(@array_value)
  set @array_value = RTRIM(@array_value)

declare @unavailable varchar(8)

set @Unavailable = 0

-- Here's the part I added

For Each V in zipcode_array
      SELECT zipcode FROM zip_tbl WHERE zipcode Like '%" & Replace(V, "'",
"''") & "%' "
      If NOT (R.EOF OR R.BOF) Then
         Unavailable = Unavailable + 1
Next

  select @array = stuff(@array, 1, @separator_position, '')
end

set nocount off

Return

Access says something about an ADO error when I go to save it.  Then it
locks up and I have to ctr-alt-del to close it out and try again.

TIA,
Jeremy