How to go to next line in the output and print a table
-
Hello Everyone,
I've the following T-SQL block that I'm executing where I want to print the following output in next line but not sure how to do this:
This is printing everything in one line:
SET @BODYTEXT = ' Dear ' + @fname +',We realize you may have taken ' + @course_due + ' in '+ @month_last_taken +'.'
How do I do this:
SET @BODYTEXT = ' Dear ' + @fname +
',We realize you may have taken ' + @course_due + ' in '+ @month_last_taken +'.'
Also how can I create a table in this variable, something like this:
(TABLE) LIST THE COURSE CODE, COURSE NAME , EMPLOYEE ID, EMPLOYEE NAME
(Course Name) (Last Completed) (Now due in Month/year)
My T-SQL code:
DECLARE @email varchar(500)
,@intFlag INT
,@INTFLAGMAX int
,@TABLE_NAME VARCHAR(100)
,@EMP_ID INT
,@fname varchar(100)
,@course_due varchar(100)
,@month_last_taken varchar(100)
,@BODYTEXT VARCHAR(MAX)
SET @intFlag =1
SET @TABLE_NAME='dbo.TEST_EMAIL'
SELECT @INTFLAGMAX = ROW_COUNT FROM dbo.FN_COUNT_ROWS (@TABLE_NAME)AS X
WHILE @intFlag <= @INTFLAGMAX
BEGIN
SELECT @EMP_ID = ID FROM (SELECT ID, ROW_NUMBER() OVER (ORDER BY ID ASC) ROW_NUM FROM dbo.TEST_EMAIL) as new WHERE ROW_NUM=@intFlag
SELECT @EMAIL = email,@fname =fname, @course_due=coursename_new, @month_last_taken=month_last_taken
FROM TEST_EMAIL
WHERE ID=@EMP_ID
SET @BODYTEXT = ' Dear ' + @fname +',We realize you may have taken ' + @course_due + ' in '+ @month_last_taken +'.'
EXEC msdb.dbo.Sp_send_dbmail
@recipients =@email,
@subject = 'Compliance Overdue',
@importance= 'high',
@body = @BODYTEXT
SET @intFlag = @intFlag + 1
END
GO
-
Try this article that does pretty much what you are looking at.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
mayankminawat (8/13/2014)
This is printing everything in one line:SET @BODYTEXT = ' Dear ' + @fname +',We realize you may have taken ' + @course_due + ' in '+ @month_last_taken +'.'
How do I do this:
SET @BODYTEXT = ' Dear ' + @fname +
',We realize you may have taken ' + @course_due + ' in '+ @month_last_taken +'.'
The link supplied above will sort you out for HTML tables. For line breaks you need to add them to your string in one of two ways.
SET @BODYTEXT = ' Dear ' + @fname + ',We realize you may have taken ' + @course_due + ' in ' + @month_last_taken + '.'
or
SET @BODYTEXT = ' Dear ' + @fname + ',' + CHAR(13) + CHAR(10) +'We realize you may have taken ' + @course_due + ' in '+ @month_last_taken +'.'
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply