How to run procedure iteratively in a loop

Question

I have a procedure to encrypt string. How can I apply it to a table so that I can encrypt all of the texts?

-- create table
create table TheTable
(word varchar(200),
 num int)
go

bulk insert TheTable
from 'E:\My documents\Desktop\testdata.txt'
with
(
fieldterminator = ',',
rowterminator = '\n'
)
go

--procedure
create procedure encrypt 
  @ptext as varchar(500)
  , @etext as varchar(500) OUTPUT
as 
begin
set nocount on
declare @key as tinyint = 3
declare @pc as varchar(1)
declare @i as smallint = 1
declare @n as smallint

set @n = len(@ptext)
set @etext = ' '

while @i <= @n
begin 
set @pc = substring (@ptext, @i, 1)

if ascii(@pc) between 48 and 57 
begin
    if ascii(@pc) + @key < 58
        set @pc = char((ascii(@pc) + @key))
    else 
        set @pc = char((ascii(@pc) + @key)-10)
end

else if ascii(@pc) between 65 and 90 
begin
    if ascii(@pc) + @key < 91
        set @pc = char((ascii(@pc) + @key))
    else 
        set @pc = char((ascii(@pc) + @key)-26)
end

if ascii(@pc) between 97 and 122 
begin
    if ascii(@pc) + @key < 123
        set @pc = char((ascii(@pc) + @key))
    else 
        set @pc = char((ascii(@pc) + @key)-26)
end

set @etext = @etext + @pc
set @i = @i + 1
end 
end

I heard cursor is not a good idea, so I am thinking to run the procedure in a loop through all cells. I wrote something like this, but it does not work.

declare @ptext varchar(200)
declare @totalrow int
declare @rownum int
set  @totalrow = @@ROWCOUNT 
set @rownum = 1 

while @rownum <= @totalcount
begin 
declare @etext char(500);
exec encrypt @ptext, @etext OUT
select @etext 
set @rownum = @rownum + 1 
end
end

How can I do it? Thanks.

Answer 1

This should do it, an example

Declare @Counter int;
Set @Counter = 1;

While @Counter < 10
Begin    
    exec [dbo].[myprocedure] @param1,@param2                    
    Set @Counter = @Counter + 1;
End

Found this thread, almost what you are looking for.

http://social.msdn.microsoft.com/Forums/en-US/344435d2-a009-4714-b739-8ea0ead636d2/calling-stored-procedure-in-a-loop-and-get-results