CODEWITHSUNDEEP
stored-proceduressql-server-2008-r2
AndresMontj
AndresMontj

Reputation: 325

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

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.

Upvotes: 2

Views: 23434

Answers (2)

Thomas
Thomas

Reputation: 64635

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

Upvotes: 5

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

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

Upvotes: 2

Related Questions