Running SQL script using SQLCMD and export to CSV file

I had a similar issue of how to use SQLCMD export to CSV the results of a query whose data may contain quotes and commas as part of the output. To handle the optional escaping and quoting needs, I knew that my query was going to be too long for inline-inclusion in the call to SQLCMD. I believe the -i option would allow you to put the query in its own file but I opted to use a stored procedure.

I created the following user-defined scalar function:

USE [MyDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Lynn Nicholson
-- Create date: 08/16/2018
-- Description:	Prep a value for output to CSV file
-- =============================================
ALTER FUNCTION [dbo].[CSV] 
(
	@text varchar(4000)
)
RETURNS varchar(4000)
AS
BEGIN
	DECLARE @escaped varchar(4000)
	SELECT @escaped = @text
	
	IF (CHARINDEX(',', @text) + CHARINDEX('"', @text)) > 0 -- if our text has a comma or single quote
	BEGIN
		SET @escaped = CONCAT('"', REPLACE(@escaped, '"', '""'), '"')	-- double-up any quotes in the text and surround with quotes.
	END
	
	RETURN @escaped

END

And given this query:

SELECT firstName, lastName, notes FROM client

You call the prep function like this:

SELECT dbo.CSV(firstName) as firstName, dbo.CSV(lastName) as lastName, dbo.CSV(notes) as notes FROM client

The "dbo." is important.

Use at your own risk and ymmv, etc.