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?

sorry, that was a typo. The end of the loop is FETCH NEXT FROM crs_clientId INTO @p_in — Robert McCraw
– Robert McCraw, Commented Jan 22, 2018 at 20:04

Robert McCraw · Accepted Answer · 2018-01-23 17:28:44Z

2

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

answered Jan 23, 2018 at 17:28

Robert McCraw

6834 gold badges8 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SSD · Accepted Answer · 2018-01-22 18:09:47Z

0

I am not sure how are you getting 1,2,3,4 in output. As code seems to have issue.

You are assigning a value to cursor in the end which seems incorrect variable

See @c_clientId in FETCH NEXT

So maybe your have posted half code.

Or you need to assign value to correct varable i.e. @p_in in end of cursor loop to get correct value

I tried to simulate with a sample procedure to return values

--sample stored procedure
create procedure usp_get_letter_for_number(@in int, @out char(1) OUTPUT)
as
begin
    if @in = 1
        set @out = 'A'
    else if @in = 2
        set @out = 'B'
    else if @in = 3
        set @out =  'C'
    else 
        set @out = 'Z'
end

And used your code and corrected it to use @pin instead of @c_clientId with sample SELECT statement

DECLARE @p_in int
DECLARE @p_out char
DECLARE crs_clientId CURSOR FOR SELECT top 10 id from Users /*Sample sql query*/
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

Output

1
A
2
B
3
C
4
Z
5
Z
:
:

edited Jan 22, 2018 at 18:09

answered Jan 22, 2018 at 16:19

SSD

1,4012 gold badges14 silver badges20 bronze badges

1 Comment

Robert McCraw Over a year ago

Sorry, that was a typo. I've fixed it.

Collectives™ on Stack Overflow

Stored procedure inside cursor not updating local variable from output parameter

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related