[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