WHILE (@@FETCH_STATUS=0)
--------------
Here are the codes from Microsoft's site at
http://msdn.microsoft.com/library/default.asp?url="">
:
@@FETCH_STATUS
Returns the status of the last cursor FETCH statement issued against any
cursor currently opened by the connection.
Return value Description
0 FETCH statement was successful.
-1 FETCH statement failed or the row was beyond the result set.
-2 Row fetched is missing.
--------------
Have you tried putting a FETCH statement before the WHILE and move the
FETCH inside the WHILE to the end of the loop? Something like this:
FETCH NEXT FROM CUR_CUSTOMER_V_IDS INTO @CURRID
WHILE (@@FETCH_STATUS=0)
BEGIN
IF @STR_IDS = ''
BEGIN
SET @STR_IDS=CAST(@CURRID AS VARCHAR(4))
END
ELSE
BEGIN
SET @[EMAIL PROTECTED]','+CAST(@CURRID AS
VARCHAR(4))
END
FETCH NEXT FROM CUR_CUSTOMER_V_IDS INTO @CURRID
END
Tom Nunamaker
> Hi,
>
> 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]
