I have wrote a stored proc which creates a cursor,
I then open it, loop within it, append the id to a variable and when
finished close it
and print out the variable.
I am testing the PROC inside Query Analyzer.
The first time it runs ok, but the second time it wont,
this is the PROC,
CREATE PROCEDURE LEO4_CURRENT_STATES_WITH_LOCATION
(@CUSTOMERID INT)
AS
DECLARE @STR_IDS VARCHAR(500)
SET @STR_IDS=''
DECLARE CUR_CUSTOMER_V_IDS INSENSITIVE CURSOR
FOR SELECT DISTINCT TABLE.VEHICLEID FROM TABLE WHERE
[EMAIL PROTECTED]
OPEN CUR_CUSTOMER_V_IDS
DECLARE @CURRID INT
WHILE @@FETCH_STATUS <> -1
BEGIN
FETCH NEXT FROM CUR_CUSTOMER_V_IDS INTO @CURRID
IF @STR_IDS = ''
BEGIN
SET @STR_IDS=CAST(@CURRID AS VARCHAR(4))
END
ELSE
BEGIN
SET @[EMAIL PROTECTED]','+CAST(@CURRID AS
VARCHAR(4))
END
END
CLOSE CUR_CUSTOMER_V_IDS
DEALLOCATE CUR_CUSTOMER_V_IDS
PRINT 'START'
PRINT @STR_IDS
PRINT 'END'
first time it runs the out put looks something like this where x is an ID
digit,
START
xxx,xxx,xxx,xx,xxxx,xxx
END
the second time I run it I get this,
START
END
I'm not sure whats happening here, I have closed the cursor and removed it
from memory.
The only way I can get it to run again is to close down my database session
window in
query anaylzer and reconnect. I have only done testing in QA not from CF,
but I would imagine
the same thing would be happening.
Would anyone have any ideas.
J
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
