In SQL, how do you add a line break to text AND display text in the Results window?

Question

I am trying to display harcoded text on two different lines and then display three tables after them. The problem I am having is getting the text and the tables to show in the same window. (is this even possible?)

I took the code from (How to insert a line break in a SQL Server VARCHAR/NVARCHAR string) and combined it with my own to get this...

DECLARE @ASTATEMENT VARCHAR(100);
SET @ASTATEMENT = 'LINE 1' + CHAR(13)+CHAR(10) + 'LINE 2';
SELECT @aStatement

SELECT * FROM table1
SELECT * FROM table2
SELECT * FROM table3

This code executes and is displayed all in the results window, but does not have the designed functionality (two seperate lines) from the first SELECT statement.

So I changed it to...

DECLARE @ASTATEMENT VARCHAR(100);
SET @ASTATEMENT = 'LINE 1' + CHAR(13)+CHAR(10) + 'LINE 2';
PRINT @aStatement

SELECT * FROM table1
SELECT * FROM table2
SELECT * FROM table3

Now, the line breaks properly, but the text is displayed in the Messages window and my tables are displayed in the Results window. Is there a way to get both to display in the Results window?

(NOTE: as an alternative I am aware that I can simply can create a temp table. populate my table with desired text, and then (SELECT *) from that temp table. However I was hoping to not have to do that.)

Try setting the output to text instead of grid (using `ctrl+t`). — jpw, Jan 30 '15 at 14:56

score 1 · Accepted Answer · answered Jan 30 '15 at 15:03

As pointed out, it's Sql Server Management Studio ignores line breaks when displaying results in grid mode.

If you really need the results to show as different rows in grid mode you could do the following:

    Select LineText
    From   (
        Select Cast('<X>'+Replace(@ASTATEMENT,CHAR(13) + CHAR(10),'</X><X>')+'</X>' As XML) As record
    ) x
    Cross Apply
    ( 
        Select fdata.D.value('.','varchar(50)') As LineText
        From x.record.nodes('X') As fdata(D)
    ) t

score 0 · Answer 2 · answered Jan 30 '15 at 15:01

You can not do this, because prints are going to Message tab and select result to Result tab.

SELECT @aStatement means give me ONE row, where value wiil be the value of variable @aStatement, so neither by selecting this variable you can get what you want.

One way I can think is just right click in query windows, Results To -> Results to Text. And execute your statements.

In SQL, how do you add a line break to text AND display text in the Results window?

2 Answers2