How to avoid sqlcmd blank line between result sets?

Question

Refering my last question on extracting SQL stored procedures into .sql files (see here) I have another question:

How to avoid or delete the sqlcmd blank line between result sets?

Reason (see MSDN) When multiple results are returned, sqlcmd prints a blank line between each result set in a batch.

This means that stored procedures (longer than 4000 chars) are split into two parts each, in syscomments and if exported with sqlcmd into text (.sql) file there will be a new line at this split point. How to remove or avoid it?

Answer 1

here is update for your batch file (it will work for > 8000 chars limit but it's easy to tune up this limit):

for /f %%a in (sp_list.txt) do sqlcmd -E -S SERVER -d DB -h-1 -Q "DECLARE @I INT, @SP1 NVARCHAR(4000), @SP2 NVARCHAR(4000) SET @I = 0 SET @SP1 = '' SET @SP2 = '' SELECT @I = @I + 1, @SP1 = CASE WHEN @I = 1 THEN text ELSE @SP1 END, @SP2 = CASE WHEN @I = 2 THEN text ELSE @SP2 END from dbo.syscomments WHERE id = OBJECT_ID('%%a') SELECT @SP1+@SP2" -o "%%a.sql"

personally I'm concerned about so large procedures.

that's a limitation, but if you have stored procedures with line numbers longer than 4000 characters, you probably have much, much bigger problems than can be solved reading this blog ... none of my stored procedures have lines greater than about 150 characters long, so that's probably not a huge deal for most people. As I said, if your lines are that long, you have bigger problems!
Adam Machanic - "Reflect" a TSQL routine

but there are also thoughts that large procedures are not an issue:

"text" field is defined as NVARCHAR(4000) so each row can only hold up to 4000 characters. However, it is not uncommon to have object code that is much larger than 4000 characters. Solomon Rutzky - Searching Objects for Text