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
@[email protected],
@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 Ids are guaranteed sequential you can use WHILE loop, otherwise use cursor. — Hamlet Hakobyan
– Hamlet Hakobyan, Commented Mar 6, 2014 at 7:03
I have tried using cursor but, am not being successful because am new to cursors. — user3335916
– user3335916, Commented Mar 6, 2014 at 7:10
You can fill the gap in your knowledge using this technet.microsoft.com/en-us/library/ms180169(v=sql.90).aspx — Hamlet Hakobyan
– Hamlet Hakobyan, Commented Mar 6, 2014 at 7:13

Almazini · Accepted Answer · 2014-03-06 09:16:56Z

1

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
@[email protected],
@subject = emp ,
@body = @tableHTML , @body_format = 'HTML', 
@profile_name = 'Sql Profile'

FETCH NEXT FROM Curs INTO @ID

END

CLOSE Curs 
DEALLOCATE Curs;

edited Mar 6, 2014 at 9:16

answered Mar 6, 2014 at 7:24

Almazini

1,8834 gold badges25 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3335916 Over a year ago

where should i keep the remaining query starting from (DECLARE @empno)

Almazini Over a year ago

Declare variables outside the cursor. Inside cursor you should assign @tableHTML variable and run your procedure to send letter.

Collectives™ on Stack Overflow

How to loop the variables in t-sql

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related