For context, I'm trying to create an email where users can see the queried data as a table. I want the data (from each column) to align at the same position for each row (hence, a table). The data in each column can vary in length. Right now, I am not focusing on the sending the email. I am focusing on simplifying text alignment and/or table queries. If you want to add about sending DB emails, feel free though; I have not moved to that part.
EDIT: I am using a loop because each solution is assigned a user. There can be different users per solution. The project goal is to send out a single email to each user that lists that user's solutions only. (I have limited say in what can be changed.) The email is a time-triggered event based on the expiration date.
When printed, I have it formatted to print as:
Solution: 1234-12-1 | Status ID: 1 | Expires: 01-01-2024 Solution: 1234-12-2 | Status ID: 12 | Expires: 01-01-2024 Solution: 56789-1 | Status ID: 1 | Expires: 01-01-2024 Solution: 56789-2-22 | Status ID: 1 | Expires: 01-01-2024 Solution: 987-654, a | Status ID: 12 | Expires: 01-01-2024
My basic goal is: (nicer is better, but it works).
Solution | Status ID | Expiration '----------------------------------- 1234-12-1 | 1a | 1-1-2024 1234-12-2 | 12b | 01-01-2024 56789-1 | 1a | 01-01-2024 56789-2-22 | 1a | 01-01-2024 987-654, a | 12b | 01-01-2024
I am using Microsoft SSMS 2019, SQL Server 2019, and Transact-SQL. Here is my current code, stripped down to the minimum. My current code works just fine, but does not achieve my desired goal. (Its not currently set up to send out emails, but I want to get the print/display formatting correct before I tackle that next step.)
DECLARE @_MaxTemp INT;
DECLARE @_Current INT = 1;
DECLARE @_SolutionName nvarchar(30);
DECLARE @_ExpirationDate nvarchar(20);
DECLARE @_StatusID nvarchar(5);
SELECT
IDENTITY(int,1,1) as TempID
,s.[Name] as SolutionName
,s.[StatusID] as StatusID
,n.[ExpirationDate] as ExpirationDate
INTO #temp
FROM [dbo].[Solution] AS s
INNER JOIN [dbo].[Notifications] AS n
ON s.[ID] = n.SolutionID
SET @_MaxTemp = (SELECT COUNT(TempID) FROM #Temp)
WHILE @_Current < @_MaxTemp
BEGIN
SET @_SolutionName = (SELECT SolutionName FROM #temp WHERE TempID = @_Current);
SET @_StatusID = (SELECT StatusID FROM #temp WHERE TempID = @_Current)
SET @_ExpirationDate = CAST((SELECT ExpirationDate FROM #TEMP WHERE TempID = @_Current) AS nvarchar(25));
SET @_
PRINT FORMATMESSAGE('Solution: %s | Status ID: %s | Expires: 01-01-2024', @_SolutionName, @_StatusID, @_ExpirationDate);
SET @_Current += 1;
END
DROP TABLE IF EXISTS #temp`
Instead of the loop, I tried something simple below. At no surprise it didn't work. ("Subqueries are not allowed in this context. Only scalar expressions are allowed.")
print (Select * from #temp)
I realize I can query the character count, set a desired length, math it, and add needed spaces to align it. However, I would need to repeat that for every cell (More columns will be pulled, but not needed for the example). As a beginner, my code will turn into spaghetti code.
I have tried looking into other stackoverflow posts, but its usually about pulling specific cells/rows, query table descriptions, or the question title does not match the context of the question. It doesn't help that some of its 10 years old when improved/new methods didn't exist yet. When I search Microsoft documentation it was about the general print statement, pulling specific cells/rows, or a list of tables (not the content). In general searches, I experience similar search issues.
Based on the multiple comments I received, avoid using SQL "Print" to create a clean formatted table. For displaying data to user outside of SSMS, use HTML and/or XML to format the data into the desired layout.
After searching, I have found some sites that provides guidance generating a HTML/XML with SQL Server.
In the comments, Yitzhak Khabinsky pointed to another StackOverflow link that covers using XML to format SQL Results:
For sending an email within the SQL Server, Microsoft has some training/document that covers SQL database email:
EDIT: I have now refactored the code. Due to privacy reason, it has been simplified, but still provides an answer. As suggested, HTML formatting and cursor/fetch is now used. However, SQL dbmail (SSMS) is still used due to management decisions.
DECLARE @Solution nvarchar(10);
DECLARE @StatusID int;
DECLARE @ExpirationDate date;
DECLARE @HTML_file nvarchar(2000);
CREATE TABLE #temp (Solution nvarchar(10), StatusID int, ExpirationDate Date)
DECLARE message_cursor CURSOR LOCAL SCROLL FOR
SELECT
[Solution],
[StatusID],
[ExpirationDate]
FROM dbo.TableName
OPEN message_cursor
FETCH NEXT FROM message_cursor INTO @Solution, @StatusID, @ExpirationDate
WHILE @@FETCH_STATUS = 0
BEGIN
IF @StatusID = 1 --solution in-use
BEGIN
INSERT INTO #temp
VALUES
(@Solution,
@StatusID,
@ExpirationDate)
END
If @StatusID = 5 --solution disposed
BEGIN
--create HTML file to send out
SET @HTML_file = CONCAT(
N'<body>
<table style="border-collapse: collapse;border: 1px solid gray;">
<tr>
<th>Solution</th>
<th>Project #</th>
<th>Expiration Date</th>
</tr>'
,CAST ((SELECT
td = Solution, ''
,td = StatusID, ''
,td = ExpirationDate, ''
FROM #temp FOR XML PATH('tr'), type) AS nvarchar(MAX))
,'</table></body>')
--executes email to user
EXEC msdb.dbo.sp_send_dbmail
@profile_Name = 'profilename'
,@recipients = 'user@email.com'
,@subject = 'Put your email title here'
,@body = @HTML_file
,@body_format = 'HTML';
DELETE FROM #temp
END
FETCH NEXT FROM email_cursor INTO @Solution, @StatusID,
@ExpirationDate
END
DROP TABLE #temp
CLOSE email_cursor
DEALLOCATE email_cursor