Stored procedure inside cursor not updating local variable from output parameter

Question

I'm having trouble with the output parameter of a stored procedure that's being executed inside the while loop of a cursor.

DECLARE @p_in int
DECLARE @p_out char

OPEN crs_clientId

FETCH NEXT FROM crs_clientId INTO @p_in

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @p_in

    EXEC dbo.usp_get_letter_for_number
             @in = @p_in, @out = @p_out OUTPUT;

    PRINT @p_out

    FETCH NEXT FROM crs_clientId INTO @p_in
END

CLOSE crs_clientId
DEALLOCATE crs_clientId

If I run this stored procedure independently, I get:

1
A
2
B
3
C

However, running it within the the cursor I get:

1
A
2
A
3
A

What am I missing? Why is the stored procedure only updating @p_out on the first pass?

Answer 1

Still not sure what exactly the problem was, but the fix was to set the @p_out to null at the beginning of each pass of the cursor. For some reason, if the @p_out wasn't null, the output of the stored procedure wouldn't write to it.

The loop now looks like this and works correctly:

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @p_in
    SET @p_out = NULL;

    EXEC dbo.usp_get_letter_for_number
         @in = @p_in, @out = @p_out output

    PRINT @p_out

    FETCH NEXT FROM crs_clientId INTO @p_in
END