June 27, 2002 at 7:06 am
Good morning everyone!
I am writing a stored procedure that takes an input parameter (@abcs_cd)supplied by an Access upfront user through ADO. A cursor is opened based on a select statement with @abcs_cd in the where clause. SOUNDEX is then used on each record in the cursor and a temp table is populated with the results. My problem is that when I check syntax in query analyser (SQL7 sp4) i keep getting the message "must declare variable last_name". I as far as I can see its declared identically to all the other variables. I am pushing my SQL skills in this one so I would appreciate any and all feedback on the sp as a whole!
Thanks
CREATE PROCEDURE procSOUND_EX @abcs_cd char(10)
--Jonathan Matt 6/26/02
--Input Paramters: abcs_cd
--Purpose: User supplies the ABACUS code to populate a cursor, then each record in the cusor
-- is tested for a match in the Person table. The first line of a matches address
-- is also captured. The results are placed in a temp table named #abcs.
AS
set nocount on
DECLARE @last_name char(50),
@first_name char(50),
@address char(50),
@prspct_id int
DECLARE name_cursor INSENSITIVE CURSOR
FOR
SELECT Last_Name, First_Name, Address
FROM AbcsMstr
WHERE COUP = @abcs_cd
OPEN name_cursor
Go
WHILE @@fetch_status <> -1
BEGIN
FETCH name_cursor INTO @last_name, @first_name, @address
SELECT Person.person_last_name, Person.person_first_name, Person.prspct_id, Prspct.hsehold_addr1_name
INTO #abcs
FROM Person JOIN Prspct ON prspct_id = prspct_id
WHERE (SOUNDEX(person_last_name) = SOUNDEX (@last_name)) AND
(SOUNDEX(person_first_name) = SOUNDEX (@first_name))
END
CLOSE name_cursor
DEALLOCATE name_cursor
June 27, 2002 at 7:29 am
Remove the GO command after OPEN name_cursor
as this denotes the end of batch of tsql statements.
Regards,
Andy Jones
.
June 27, 2002 at 8:32 am
Thanks Andy! The little things will get ya every time.
New question if I may. I just created the sp and executed it. This brought up a problem. My SELECT INTO statement is inside the cursor loop so after the #abcs table is created on the first pass I get the message "object #abcs already exits". Any thoughs on how I can populate this table as I loop through the cursor? The cusor may consist of 10 of thousands of records so I would like to not log the activity.
June 27, 2002 at 8:58 am
You can explicitly create the temp table before opening the cursor (create table #abcs person_last_name ....). Then insert into it like:-
insert into #abcs
SELECT Person.person_last_name, Person.person_first_name, Person.prspct_id, Prspct.hsehold_addr1_name
INTO #abcs
FROM Person JOIN Prspct ON prspct_id = prspct_id
WHERE (SOUNDEX(person_last_name) = SOUNDEX (@last_name)) AND
(SOUNDEX(person_first_name) = SOUNDEX (@first_name))
instead of using the select into syntax.
Regards,
Andy Jones
.
June 27, 2002 at 9:08 am
a propos of the usage of cursors, suggest you declare the cursor as FAST FORWARD since you are only scrolling forward and reading data, this will be more efficient than just INSENSITIVE
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
June 27, 2002 at 10:48 am
Thanks all for your help! I think I need to rethink my stratagy. I am trying to find matches in our prospect table to names on mailing lists. Typically, the mailing list will have 10,000 records. Our prospect list has around 500,000 records. I just ran the stored procedure (in a test enviroment!) (this was before I saw your suggestion of fast forward) and found that the SOUNDEX functions on first and last name returned about 10-20 hits for each name in the mailing list. That means 20 x 10,000! Needless to say I killed the process after about 30 minutes, before the transaction log ate my hard drive. Any thoughts on another approach?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply