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

rivkamakAsked:
Who is Participating?
 
Leo TorresSQL DeveloperCommented:
try

DECLARE  @MyVariable varchar(255),@MyVariable2 varchar(255)	

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

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


			EXEC msdb.dbo.sp_send_dbmail

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

Open in new window

0
 
Leo TorresSQL DeveloperCommented:
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

0
 
rivkamakAuthor Commented:
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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.