Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-12-01 : 15:23:46
|
Jim writes "[SQL 6.50.201 on Windows NT 4.00.1381, SP 6]
(Yes, I know 6.5 is evil incarnate, but I'm not at liberty to update the stupid thing right now. Trust me, my degree of loathing is mighty indeed.)
I'm having a wacky issue with 6.5 implementing a stored procedure, and it's driving me absolutely crazy. Without going into unnecessary detail, we have a series of SP'd match processes that check client information in one table against information in another table (or in this case, a pair of joined tables). The resulting match information is stored in another table. These procedures have been in existence for over a year and run reasonably well and pretty reliably. I use Enterprise Mgr's SQL Query Tool to run the SPs.
So here's the problem. We had to add a new matching routine (at the client's request), which I based on an existing (i.e., working) procedure. As with all these beasts, it uses a cursor (and boy do I love 'em--Not!) to temporarily store the results of a join query, then loops through the cursor's contents to make appropriate entries in another table.
The procedure as currently written worked fine on a small data set (about a thousand entries), but it simply doesn't work on the larger one I've attempted to work with. At all.
I've tried breaking down the various parts of the query, and they all run fine individually, yet don't do a thing when put together. At first assuming it was a problem with the query, I've checked permissions on the tables, and rebuilt indexes on all tables consulted. Didn't help. It's driving me in-bloody-sane, as well as making my workday miserable. HELP!
Here's the procedure:
----------------------
CREATE PROCEDURE prcMatching29 @ClientNo int AS
DECLARE @msg varchar(40)
DECLARE @PERSID int
DECLARE @ClientID varchar(13)
DECLARE @SelectLevel varchar (20)
DECLARE @rCount int
DECLARE results29Cursor CURSOR
FOR
SELECT InsAddr.PERSID, ClientID
FROM Person
INNER JOIN InsAddr
ON InsAddr.PERSID = Person.person_id
INNER JOIN ClientData
ON ClientData.LName = Person.last_name
AND InsAddr.ADDRESS1 = ClientData.H_STR1
WHERE ClientData.ClientNo = @ClientNo
FOR READ ONLY
OPEN results29Cursor
FETCH NEXT FROM results29Cursor INTO @PERSID, @ClientID
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SELECT @rCount = (SELECT Count(*)
FROM ClientResults
WHERE ClientNo = @ClientNo
AND PERSID = @PERSID
AND ClientID = @ClientID)
IF (@rCount = 0)
BEGIN
INSERT INTO ClientResults (ClientNo, PERSID, SelectLevel, ClientID, SelLevel, verlevel)
VALUES (@ClientNo, @PERSID, '9.5', @ClientID, 9.5, 2)
END
ELSE
BEGIN
SELECT @SelectLevel = (SELECT SelectLevel FROM ClientResults WHERE ClientNo = @ClientNo AND PERSID = @PERSID AND ClientID = @ClientID)
IF (PATINDEX(@SelectLevel, '%9.5%') = 0)
BEGIN
UPDATE ClientResults
SET SelectLevel = SelectLevel + ',9.5'
WHERE ClientNo = @ClientNo
AND PERSID = @PERSID
AND ClientID = @ClientID
END
END
END
FETCH NEXT FROM results29Cursor INTO @PERSID, @ClientID
END
CLOSE results29Cursor
DEALLOCATE results29Cursor
EXEC prcMatchingCleanup @ClientNo
GO -------
Before you ask, I wasn't the guy who designed the system, just the guy that maintains it and adds features...
The procedure called at the end isn't affecting it either--it's not getting to that stage (and I've tried running it without the call as well). I feel like I've been over every inch of this thing without finding ANY leads.
Any help you can give is very much appreciated....
-Jim" |
|
|
|
|
|
|
|
SQL 6.5 Stored Proc Failing?