asked on

loop through sql statement

I am trying to loop through a sql select statement.
and on each row run a sendemail to the data from that row.

I copied this code from something else and I am not sure that I took too much and what can be removed.
I have something like this, but it's not 100% correct because the while loop is throwing an error.

declare csrIds cursor static for

open csrIds

while 1 = 1
begin
    fetch next from csrIds into @id
    if @@FETCH_STATUS <> 0
        break

 select @email = bd.Email, @name =  bd.FirstName
    from  TestEmail bd
    where
      where sentEmail<> 1

DECLARE @Sub nvarchar(MAX)
SET @Sub = 'this is a record that came through: ' + @email + ' and name of : ' + @name

EXEC msdb.dbo.sp_send_dbmail
  @body_format= 'HTML',@recipients= @email ,@subject = 'Cash Receipts' ,
   @body=@Sub ;

end --while

deallocate csrIds

Open in new window

Leo Torres

you have to use a cursor

DECLARE  @MyVariable INT,	

DECLARE My_Cursor CURSOR FAST_FORWARD FOR
Select col1 from MyTable  

OPEN My_Cursor
	FETCH NEXT FROM My_Cursor INTO @MyVariable
	WHILE @@FETCH_STATUS =0
	BEGIN 
	
	--Your iteration Here
	
FETCH NEXT FROM My_Cursor  INTO @MyVariable 
END
CLOSE My_Cursor DEALLOCATE My_Cursor

Open in new window

rivkamak

ASKER

I tried putting in my select statement.
Did I do it wrong?

DECLARE  @MyVariable INT,	

DECLARE My_Cursor CURSOR FAST_FORWARD FOR
 select @email = bd.Email, @name =  bd.FirstName
    from  TestEmail bd
    where
      where sentEmail<> 1

OPEN My_Cursor
	FETCH NEXT FROM My_Cursor INTO @MyVariable
	WHILE @@FETCH_STATUS =0
	BEGIN 
	
DECLARE @Sub nvarchar(MAX)
SET @Sub = 'this is a record that came through: ' + @email + ' and name of : ' + @name


			EXEC msdb.dbo.sp_send_dbmail

   @body_format= 'HTML',@recipients= @email ,@subject = 'Cash Receipts' ,
    @body=@Sub ;
	
FETCH NEXT FROM My_Cursor  INTO @MyVariable 
END
CLOSE My_Cursor DEALLOCATE My_Cursor

Open in new window

ASKER CERTIFIED SOLUTION

Leo Torres

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial