Inserting a carriage return in dynamic sql output

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Inserting a carriage return in dynamic sql output

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-02-20 : 07:52:29

Is there a way to insert a new line when using dymanic sql?

E.g.
DECLARE @SQL NVARCHAR(MAX)

SET @SQL = 'IF OBJECT_ID('ViewName','v') IS NOT NULL BEGIN DROP VIEW [ViewName] END CREATE VIEW......'

DESIRED OUTPUT:
'IF OBJECT_ID('ViewName','v') IS NOT NULL BEGIN DROP VIEW [ViewName] END
CREATE VIEW...... -- So this starts on a new line

Thanks

Hearty head pats

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-02-20 : 07:55:24

Using Text mode in query analyser

DECLARE @SQL NVARCHAR(MAX)
SET @SQL = 'IF OBJECT_ID(''ViewName'',''v'') IS NOT NULL BEGIN DROP VIEW [ViewName] END '+char(13)+'CREATE VIEW......'
print @sql

Madhivanan

Failing to plan is Planning to fail

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-02-20 : 08:05:15

Hi Madhivanan

Thanks for the reply.

How about if I was creating a view/procedure/table/etc dynamically within a procedure, that is called by a job? Therefore, the porcedure would be executed, and I would want to include the formatting within the dynamic sql in the procedure:

'DROP View blah blah ' CARRIAGE RETURN HERE ' CREATE VIEW.....'

Is that actually possible?

Thanks

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-02-20 : 08:36:54

Solution: USE CHAR(10)

DECLARE @SQL NVARCHAR(MAX)
SET @SQL=
'CREATE VIEW ViewName'+CHAR(10)+'AS'+CHAR(10)+'SELECT * FROM Test'

Hearty head pats

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-02-20 : 08:40:53

UMN -- I must be missing something. What's wrong with:


DECLARE @sql NVARCHAR(MAX)

SET @sql = '
IF OBJECT_ID('ViewName','v') IS NOT NULL BEGIN
    DROP VIEW [ViewName]
END

CREATE VIEW......'

EXEC (@sql)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-02-20 : 09:09:10

Hi Charlie

The view is created dyamically by referencing existing template objects, therefore, formatting cannot be applied. The formatting has to be done using dynamic SQL.

So in the example (create view), the columns are identified on the fly, and formatting applied at that point.

while @ColumnCount <> 1
begin
SELECT @sql = @sql +CHAR(10)+', '+ColumnName
FROM sys.tables WHERE OBJECT_NAME(object_id) = template.TableName...... etc

set ColumnCount = ColumnCount -1
end

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-02-20 : 10:23:52

I see.

Well in that case -- you'll want to use


CHAR(13) + CHAR(10)

(carriage Return - Line feed) as this is the windows format for end of line. Otherwise you could be in trouble

a single CHR(13) is the unix format
I don't know of anything that marks end of line with only CHR(10) (Line Feed)

Also -- FYI CHAR(9) is a TAB.

Example


DECLARE @sql NVARCHAR(MAX)

DECLARE @foo TABLE (
	[ID] INT
	, [line] NVARCHAR(255)
	)

SET @sql = ''

INSERT @foo
      SELECT 1, 'IF OBJECT_ID(''ViewName'',''v'') IS NOT NULL BEGIN'
UNION SELECT 2, CHAR(13) + CHAR(10)
UNION SELECT 3, CHAR(9) + 'DROP VIEW ViewName' + CHAR(13) + CHAR(10)
UNION SELECT 4, 'END'

SELECT @sql = @sql + [line] FROM @foo ORDER BY [Id] ASC

PRINT @sql

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources