
In the Last Episodes…
Hi there! Welcome to the 10th instalment of a series of tutorials on Sql and SQL Server Studio. There’s a simple goal: To make you familiar and comfortable with the tool, and the language. "Why does it even matter?" I see you asking. It turns out that curiosity and side projects are often key in getting picked on new projects or even getting hired for a new job. The mere fact you’ve already used an important tool such as SQL Server Studio and wrote some SQL queries, can and will give you a clear head start.
- In Episode 1, I show you how to set up our environment and local server – Part 1 Step by Step Setup of SQL Server Studio
- In Episode 2, we cover how to create your own databases, tables, and an important commentary on naming conventions – Part 2 Databases, Tables & Naming Convention
- In Episode 3, we introduce the CRUD operations and the Primary and Foreign Keys – Part 3 CRUD Operations, Primary & Foreign Keys
- In Episode 4, we discuss schemas & the main normalization steps – Part 4 Schemas & Normalization
- In Episode 5, we cover Stored Procedures & Scheduling, this is a real beast. I can’t stress enough how this will facilitate and automate your daily (data-)life. Part 5 Stored Procedures & Scheduling
- In Episode 6, we introduce SSIS Packages for ETL, and reviewed out to import and export data between two databases, and between a database and Excel. Part 6 SSIS Packages Introduction
- In Episode 7, we connect SQL Studio to PowerBI and build our first visuals. Part 7 Connect to PowerBI & First Visuals.
- In Episode 8, we leverage the power of SQL Views, an easy and lightweight tool to combine and retrieve complex tables. You won’t believe how you could live without them. Part 8 Leverage The Views
- In Episode 9, we take advantage of temporary tables, to temporarily store and retrieve data within stored procedures. It’s extremely useful to find out intermediary results, and cache data in a table form for later use. Find out more in Part 9 Temporary Tables
And don’t forget to come back 😉 .
What to Expect?
After the Views and Temp Tables, it’s time to dive into a feature that does not do not get much publicity but can prove quite useful. By the end of this Tutorial, you’ll feel empowered to leverage the power of report generation within SQL procedures. Let’s jump right in!
Introduction
There are times when, despite having your data displayed on a fancy PowerBI report, users still require access to raw data. Raw as in .csv, for instance. While it’s common courtesy to write a quick query on that famous table-with-everything (spoiler: there’s no such thing), it can become overwhelming if the users are requesting this frequently. A solution would be to schedule a job and to attach to it a .csv document with the requested information.
Thankfully, there’s a helpful function with a set of parameters to tweak to obtain that report. The solution is not right out the box, it requires a bit of creativity, but as you will find out it is quite simple to implement.
Export SQL data as CSV by email
We assume your SQL Studio environment is up and running. If you need help, here’s a step by step guide: Part 1 Step by Step Setup of SQL Server Studio.
For this tutorial, we will leverage a stored procedure and SSMS scheduling capabilities, some parts might be rushed, you will find more information here: Part 5 Stored Procedures & Scheduling
The table we will leverage is very simple and consist of "First Name" and "Last Name"
- Let’s create a procedure. The typical skeleton offered by SSMS is:
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
AS
BEGIN
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO
Some boilerplate code that won’t be needed in our case has been removed.
- We start by declaring a parameter _@query_ and assigning it the type NVARCHAR(MAX)
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
AS
BEGIN
DECLARE @query NVARCHAR(MAX)
- We then start to define the parameter _@query_:
SET @query = N'....'
with N defining the following string as being an NVARCHAR,
- We then set the below statement to prevent the message from informing us of the number of rows impacted by the query.
SET @query = N'
SET NOCOUNT ON
Thanks to the above we won’t be getting this in our CSV extract:

- Later comes the well-known SELECT statement, with sets of double single quotes, to respect the string format. For our fictional table with columns [FirstName] and [LastName] this gives:
SET @query = N'
SET NOCOUNT ON
SELECT
''FirstName'' as FirstName,
''LastName'' as LastName
Note that in this case, we’re not referring to a table, we’re just preparing the headers of our CSV export.
- We will then UNION the results from our above statement with data resulting from our second statement, that is data from our table:
UNION
SELECT
[FirstName] AS [FirstName],
[LastName] AS [LastName]
FROM [myDatabase].[dbo].[Users]
- We then close this string by turning off our NOCOUNT statement
SET NOCOUNT OFF'
- As a result, this first part looks like this:
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
AS
BEGIN
DECLARE @query NVARCHAR(MAX)
SET @query = N'
SET NOCOUNT ON
SELECT
''FirstName'' as FirstName,
''LastName'' as LastName
UNION
SELECT
[FirstName] AS [FirstName],
[LastName] AS [LastName]
FROM [myDatabase].[dbo].[Users]
SET NOCOUNT OFF'
- At this stage, it’s good practice to try and run the code starting at ‘DECLARE’ and ending with the ‘SET NOCOUNT OFF’ statement. Because it is part of the <@query> string variable, any typos, an extra or missing comma will not be highlighted by the SSMS IDE.
To test it, copy the part highlighted in bold and paste it to a new screen. For the first select statement, remove one single quote from each end of the column names. Then run the statement. It should return the table.
- We will then leverage an SSMS function to send out an email, and provide parameters information to add this data as a CSV attachment.
EXEC msdb.dbo.sp_send_dbmail
@profile_name= 'Reporting Team',
@recipients= '[email protected]',
@subject= 'Names CSV export',
@body= 'Hello, <br><br> Please find attached your CSV export <br><br> Reporting Team',
@body_format = 'HTML',
This first part is straightforward, where we provide the profile name, recipient, subject, body… Most of those parameters can be fed with variables if needed. In our case, we’re providing some hardcoded dummy values. Add your email address to make sure you’ll get the file.
The interesting part comes next:
@query = @query,
@attach_query_result_as_file = 1,
@query_attachment_filename = 'ExportResults.csv',
@query_result_separator = ',',
@exclude_query_output = 1,
@query_result_header = 1,
@query_no_truncate = 1,
@query_result_no_padding = 0,
@query_result_width = 8192
END
Above we first provide our NVARCHAR string <@query> to the query parameter and define that a file will be attached, with its name. We can then define the separator. The other parameters ensure the exported CSV is lean and clean, with maximal width and no padding.
To recap, our entire code for this procedure is, therefore:
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
AS
BEGIN
DECLARE @query NVARCHAR(MAX)
SET @query = N'
SET NOCOUNT ON
SELECT
''FirstName'' as FirstName,
''LastName'' as LastName
UNION
SELECT
[FirstName] AS [FirstName],
[LastName] AS [LastName]
FROM [myDatabase].[dbo].[Users]
SET NOCOUNT OFF'
EXEC msdb.dbo.sp_send_dbmail
@profile_name= 'Reporting Team',
@recipients= '[email protected]',
@subject= 'Names CSV export',
@body= 'Hello, <br><br> Please find attached your CSV export <br><br> Reporting Team',
@body_format = 'HTML',
@query = @query,
@attach_query_result_as_file = 1,
@query_attachment_filename = 'ExportResults.csv',
@query_result_separator = ',',
@exclude_query_output = 1,
@query_result_header = 1,
@query_no_truncate = 1,
@query_result_no_padding = 0,
@query_result_width = 8192
END
Let’s select the code from BEGIN to END and run it… An email should be underway with the file attached!
Commons Bugs
The above can be quite powerful, but it can require some patience to implement as bugs can hide within the query string we declare in the beginning, and those are typically not picked up by the SSMS IDE.
- Too few or too many single quotes, e.g.
''FirstName' as FirstName,
- Too many or missing commas (especially if you copied-pasted a lot and adjusted lines, just before the UNION statement), e.g.
''FirstName' as FirstName,
''LastName'' as LastName,
UNION
- A DateTime format which should be recast as a VARCHAR, e.g.
-- REPLACE THIS
...
[BirthDate],
...
-- BY THIS
...
CAST([BirthDate] AS VARCHAR),
...
- Ensure you have the same number of columns on both sides of your SELECT statements,
- Double and triple-check that all the mentioned columns are actually part of the table you’re querying – obvious, but can still happen.
If you’re still stuck:
- Take a deep breath, maybe take a break,
- Start with a column or two, get it right, then move up to more columns
- And test, test, test relentlessly 😉 .
Once it’s under control, you can wrap this procedure within a scheduled job, and inform your users that they’ll soon get that report by email 🎉 .
Final Words and What’s Coming Next
That’s it for now! I hope you feel more confident creating and leveraging SQL procedures to generate and export CSV files based on underlying queries and then sending them by email to users.
- It can be a good alternative or complement to your existing data visualization reports,
- It can be used as an audit tool, or as proof, with weekly exports of what’s in the database at any given point,
- It can also be leveraged if connected to no-code or low-code tools, with triggers that would then start this SQL procedure and provide data to the user.
Thanks for reading, let me know what you think or if there’s a topic I should cover. In the meantime, feel free to subscribe and follow me. Until next time!
Happy Coding 🎉 !
Thanks for reading! Enjoyed this story? Join Medium for complete access to all my stories.