3

This may be a very simple but it's still not clear to me. I'm writing a SQL Agent job to generate output in html format.

I need to get results of a query SELECT COUNT(*) FROM TABLE_NAME. I'm able to get output generated with actual data in html but I just need count from table.

What is the easiest way to do it?

Here is part of my query:

SET @tableHTML = N'<table border="1">' +
        N'<FONT SIZE="3" FACE="Calibri">' +
        N'<tr><th align="center">COLUMN_NAME</th>' +
        N'</tr>' +
        ISNULL(CAST ( ( SELECT  td = [COLUMN_NAME],''
                        FROM dbo..TABLE_NAME
                    FOR XML PATH('tr'), TYPE 
        ) AS NVARCHAR(MAX) ),'') +
        N'</FONT>' +
        N'</table>'

Can someone please help me to get output as count instead of individual row in output.

I have couple staging tables for a process and need to get notification for those tables with data in it. First table I only need row count. There is only one column in it. And second table has 4 Columns and I got expected output for it in sql job. Actually I just need to add one line with total rows from table to output in mail. I hope this will help to understand the question better.

It doesn't need to be in HTML but my second table needs to be included in output makes it easy to generate proper table output. I'm not much familiar with sql stuff yet. Still learning.

10

I think it is a VERY bad plan to be generating HTML via SQL. There are many many ways this can go wrong, without even mentioning the maintenance nightmare that this will inevitably cause.

All that being said your solution might be as simple as adding the following:

+ CAST((SELECT COUNT(*) FROM dbo.TABLE_NAME) AS VARCHAR(20)) +

The reason why I think you are having problems is because SQL Server requires you to explicitly convert your integers to strings.

  • I think explaining why it's a very bad idea, may be more useful to the OP than the answer. – Evan Carroll Jan 2 '17 at 21:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.