How to make a loop calling a stored procedure in SQL Server 2008 R2?

Question

I need to call a stored procedure several times. How can I put this in a loop?

DECLARE @return_value int,
                @sinresultado varchar(200),
                @svcmensaje varchar(200)

EXEC    @return_value = [dbo].[ppocfdcancelacionwsejecuta]
                @evcrfcemisor = N'BBA940707IE1',
                @evcserie = N'XXAABB',
                @evcfolio = N'666',
                @sinresultado = @sinresultado OUTPUT,
                @svcmensaje = @svcmensaje OUTPUT

I need to make @evcfolio to run from 1 to 10 in the same loop. Like a for in java and the value of evcfolio increase in 1 every loop.

Thanks for your time.

Solution 1

Declare @Counter int;
Declare @CounterText nvarchar(10);
Declare @return_value int,
                @sinresultado varchar(200),
                @svcmensaje varchar(200);
Set @Counter = 666;

While @Counter < 676
Begin
    Set @CounterText = Cast( @Counter As nvarchar(10));
    exec @return_value = [dbo].[ppocfdcancelacionwsejecuta]
                    @evcrfcemisor = N'BBA940707IE1',
                    @evcserie = N'XXAABB',
                    @evcfolio = @CounterText,
                    @sinresultado = @sinresultado OUTPUT,
                    @svcmensaje = @svcmensaje OUTPUT
    Set @Counter = @Counter + 1;
End

Solution 2

While I agree that restructuring the stored procedure and having it deal with the loop (or a set in a TVP) instead of calling the procedure multiple times is a better approach, in the meantime, loops in any language are fairly straightforward and you can do something like this:

DECLARE @i INT;
SET @i = 666;

WHILE @i <= 675
BEGIN
    EXEC ... @evcfolio = @i ...;
    SET @i += 1;
END

Comments

[Adir D]

SET @Counter = 666; While @Counter <= 10? Something not right there... you'll never enter your WHILE block.

[Adir D]

Thanks for the correction, however now you will execute the procedure 11 times, not 10.