How to loop the variables in t-sql

Question

I have a t-sql i have to loop the variables.

DECLARE @empno  nVARCHAR(MAX),@tableHTML  NVARCHAR(MAX) ;
set @empno  =   (select Employee_No from emptemp where active = 'Yes');
SET @tableHTML =
N'<H2>Additions</H2>' +
N'<table border="1">' +
N'<th>Ename</th>' +
N'<th>Sal</th>' +
'<tr>' +
CAST ( ( select td=ename,'',td=sal,'' from emp Where empno = @empno)
FOR XML PATH('tr'), TYPE  ) AS NVARCHAR(MAX) ) + N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail
@recipients=someone@gmail.com,
@subject = emp ,
@body = @tableHTML , @body_format = 'HTML', 
@profile_name = 'Sql Profile'

The Emptemp table looks like

id  empno   active
1   245     yes
2   124     yes
3   255     yes
4   224     No

I have to send the individual mail based on the data in emp table

If your `Id`s are guaranteed sequential you can use `WHILE` loop, otherwise use cursor. — Hamlet Hakobyan, Mar 06 '14 at 07:03
I have tried using cursor but, am not being successful because am new to cursors. — user3335916, Mar 06 '14 at 07:10
You can fill the gap in your knowledge using this http://technet.microsoft.com/en-us/library/ms180169(v=sql.90).aspx — Hamlet Hakobyan, Mar 06 '14 at 07:13
Look for other SO posts like [this one](http://stackoverflow.com/questions/886293/how-do-i-execute-a-stored-procedure-once-for-each-row-returned-by-query) or [this one](http://stackoverflow.com/questions/1656804/sql-call-stored-procedure-for-each-row-without-using-a-cursor) — Mr Moose, Mar 06 '14 at 07:18

Almazini · Accepted Answer · 2014-03-06T09:16:56.713

That is how cursor will look like:

DECLARE @empno  nVARCHAR(MAX)
DECLARE @tableHTML  NVARCHAR(MAX) ;
DECLARE @ID INT;
DECLARE Curs CURSOR FAST_FORWARD
FOR
    SELECT DISTINCT  ID
    FROM    dEmptemp 
    WHERE active = 'Yes'
    ORDER BY ID

OPEN Curs

FETCH NEXT FROM Curs INTO @ID

WHILE @@FETCH_STATUS = 0

BEGIN 

set @empno  =   (SELECT empno FROM emptemp WHERE ID = @ID);
SET @tableHTML =
N'<H2>Additions</H2>' +
N'<table border="1">' +
N'<th>Ename</th>' +
N'<th>Sal</th>' +
'<tr>' +
CAST ( ( select td=ename,'',td=sal,'' from emp Where empno = @empno)
FOR XML PATH('tr'), TYPE  ) AS NVARCHAR(MAX) ) + N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail
@recipients=someone@gmail.com,
@subject = emp ,
@body = @tableHTML , @body_format = 'HTML', 
@profile_name = 'Sql Profile'

FETCH NEXT FROM Curs INTO @ID

END

CLOSE Curs 
DEALLOCATE Curs;

where should i keep the remaining query starting from (`DECLARE @empno`) — user3335916, Mar 06 '14 at 08:58
Declare variables outside the cursor. Inside cursor you should assign @tableHTML variable and run your procedure to send letter. — Almazini, Mar 06 '14 at 09:10

How to loop the variables in t-sql

1 Answers1