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

Ken Schaefer ken at adOpenStatic.com
Sun Dec 8 18:42:00 CST 2002


Oh boy :-)

Couple of things:

a) In answer to the question "What tools can I use to manage my SQL Server
databases, instead of Access?" - SQL Server comes with Enterprise Manager
and Query Analyser. Just about everything can be done using Query Analyser
(though creating somethings in EM, then saving the resulting script file, in
case you need to recreate the object eg a DB maintenance plan, is faster)

b) You have a bit of VBScript code inside the stored procedure - that will
never fly. SQL Server has its own "lanaguage" called T-SQL, which is a
superset of SQL.

In order to do what you want, I would
- parse the input string
- store the individual values in a #temp table (this in an in-memory table)
- JOIN the #temp table back onto your main data table to get the fields you
want.

Something like:

CREATE PROCEDURE IsZipAvailable

    @ZipCodeList         varChar(1000)
    @separator             varChar(1)

AS

    SET NOCOUNT ON

    DECLARE @Left AS int
    DECLARE @remainderOfZipCodeList AS varchar(1000)

    SET @remainderinSaleIDs = @ZipcodeList

    BEGIN TRAN ParseZipCodes

    -- Create a Temporary Table
    CREATE TABLE #Temp (
        ZipCode    varChar(10)
        )

    -- Find First Delimiter
    SET @Left = CHARINDEX(',', @remainderOfZipCodeList)

    -- Whilst delimiters still exist
    WHILE @Left > 0
    BEGIN

        -- Place next ZIP code into #temp table
        INSERT INTO
            #temp
        VALUES (
            SUBSTRING(@remainderOfZipCodeList, 1, @Left - 1)
        )

        -- If error roll back trans
        -- Return -1
        IF @@ERROR != 0
        BEGIN
            ROLLBACK TRAN ParseZipCodes
            RETURN -1
        END

        -- Set var to remaining string
        SET @remainderinSaleIDs =
RTRIM(SUBSTRING(@remainderOfZipCodeList, @Left +
1,LEN(@remainderOfZipCodeList)))

        -- Find next delimiter (if any)
        SET @Left = CHARINDEX(',', @remainderOfZipCodeList)

    END

    -- See if there are an items left (after the last delimiter)
    IF LEN(@remainderOfZipCodeList) > 0
    BEGIN
        INSERT INTO
            #temp
        VALUES (@remainderOfZipCodeList)
    END

    -- Now we have a #Temp table with all our Zip codes

    SELECT
        a.Field1,
        a.Field2,
        a.Field3
    FROM
        ourMyTable a
    INNER JOIN
        #Temp b
    ON
        a.ZipCodes = b.ZipCodes
    -- Add any WHERE criteria etc

    SET NOCOUNT OFF

GO


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Jeremy Weiss" <jweiss03 at comcast.net>
Subject: [thelist] [long] trying to convert VBScript into a stored proc


: 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
:
:
: --
: * * Please support the community that supports you.  * *
: http://evolt.org/help_support_evolt/
:
: For unsubscribe and other options, including the Tip Harvester
: and archives of thelist go to: http://lists.evolt.org
: Workers of the Web, evolt !




More information about the thelist mailing list