Here's a while example from sql server books online, I've also included
an example of using CDONTS from a stored procedure from some stuff I'm
working on below the while example

B. Using WHILE within a procedure with cursors
The following WHILE construct is a section of a procedure named
count_all_rows. For this example, this WHILE construct tests the return
value of @@FETCH_STATUS, a function used with cursors. Because
@@FETCH_STATUS may return -2, -1, or 0, all three cases must be tested.
If a row is deleted from the cursor results since the time this stored
procedure was executed, that row is skipped. A successful fetch (0)
causes the SELECT within the BEGIN...END loop to execute.

USE pubs
DECLARE tnames_cursor CURSOR
FOR
   SELECT TABLE_NAME 
   FROM INFORMATION_SCHEMA.TABLES
OPEN tnames_cursor
DECLARE @tablename sysname
--SET @tablename = 'authors'
FETCH NEXT FROM tnames_cursor INTO @tablename
WHILE (@@FETCH_STATUS <> -1)
BEGIN
   IF (@@FETCH_STATUS <> -2)
   BEGIN   
      SELECT @tablename = RTRIM(@tablename) 
      EXEC ('SELECT ''' + @tablename + ''' = count(*) FROM ' 
            + @tablename )
      PRINT ' '
   END
   FETCH NEXT FROM tnames_cursor INTO @tablename
END
CLOSE tnames_cursor
DEALLOCATE tnames_cursor

**********************************************************************
**********************************************************************
CDONTS from stored proc to send mail:


ALTER PROCEDURE spSendMail (@FROM NVARCHAR(255), @TO NVARCHAR(255),
@SUBJECT NVARCHAR(255), @BODY NVARCHAR(4000),@MAILFORMAT
tinyint,@BODYFORMAT tinyint) AS

DECLARE @Object int
DECLARE @Hresult int
DECLARE @ErrorSource varchar (255)
DECLARE @ErrorDesc varchar (255)
DECLARE @V_BODY NVARCHAR(4000)

DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)

EXEC @Hresult = sp_OACreate 'CDONTS.NewMail', @Object OUT

IF @Hresult = 0 begin

	--SET SOME PROPERTIES

	SET @V_BODY =  '' + @BODY

	EXEC @Hresult = sp_OASetProperty @Object, 'From', @FROM
	EXEC @Hresult = sp_OASetProperty @Object, 'MailFormat',
@MAILFORMAT
	EXEC @Hresult = sp_OASetProperty @Object, 'BodyFormat',
@BODYFORMAT
	EXEC @Hresult = sp_OASetProperty @Object, 'To', @TO
	EXEC @Hresult = sp_OASetProperty @Object, 'Subject', @SUBJECT
	EXEC @Hresult = sp_OASetProperty @Object, 'Body', @V_BODY

	--CALL SEND METHOD
	EXEC @Hresult = sp_OAMethod @Object, 'Send', NULL

	--DESTROY THE OBJECT
	EXEC @Hresult = sp_OADestroy @Object
end
else begin
	   EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT 
	   SELECT hr=convert(varbinary(4),@hr), Source=@src,
Description=@desc
end




-----Original Message-----
From: Gillian Leech [mailto:gillianleech@h...] 
Sent: Thursday, December 05, 2002 3:23 PM
To: sql language
Subject: [sql_language] WHILE LOOP - SQL SERVER


Hi

Does anyone know how to write a loop which will go through a table and
get 
a UserID, then from the UserID get the rest of the results belonging to 
that UserID and email the results to the User.  Then loop through the 
table again, getting and emailing the results of the next UserID.  Until

all users in that table have been emailed?

e.g. tblMyTable has the fields
UserID, InformationField1, InformationField2, InformationField2

With the information

Bloggs, BloggsInfo1, BloggsInfo2, BloggsInfo3
Joe, JoeInfo1, JoeInfo2, JoeInfo3

etc.....

I want to loop through the table, get the first UserID, in this case, 
Bloggs and email the information to him, then loop through the table
again 
this time getting the UserID 'Joe' and emailing his information, and
loop 
through the table until all users have been emailed their information

Or is there another way of doing this

Gill