Adding a Carriage Return to sql statement???

Question

Adding a Carriage Return to sql statement???

Michelle-164472

SSCommitted

Points: 1715
More actions
November 5, 2006 at 6:29 am

#170952

How do I do something like this???
replace this:
exec usp_MyProc GO
exec usp_MyProc GO
exec usp_MyProc GO
exec usp_MyProc GO
with this:
exec usp_MyProc
go
exec usp_MyProc
go
exec usp_MyProc
go
exec usp_MyProc
go
Thanks...Michelle

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 1

Use a word processor like MS-Word to do a search and replace... the symbol for a hard return in MS-Word is "^p" without the quotes.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

or add the return in the code that generates the statement...

vbcrlf in vb

char(13) + char(10) in sql server.

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 3

If this is being generated by T-SQL you can use multiline strings. The problem is that the GO command in a quoted string works just like GO anywhere else, it breaks the command into two batches and will cause syntax errors. You can make it work by closing one string literal after the line break and starting a new one with GO.

SET @cmd = 'exec usp_MyProc

' + 'GO

exec usp_MyProc'

cmille19 SSCertifiable Points: 5970 More actions · Answer 4

Use regular expressions (vim, grep, sed, awk, Perl, etc.): Here's a sed one-liner:

sed -e "s/GO/\nGO/g" myfile.sql

Michelle-164472 SSCommitted Points: 1715 More actions · Answer 5

Thank you all...I used the SED command and it all worked out fine...I would love to use it during my sql generation but I'm not sure where it would go:

SELECT REPLACE(REPLACE(REPLACE('exec my_proc ''XX'',''YY''

ZZ','XX',NAME),'YY',LEFT(NAME,2)),'ZZ','GO') FROM MASTER..SYSDATABASES WITH(NOLOCK)

WHERE DBID>5

Thanks...Michelle.

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 6

SELECT REPLACE(REPLACE('exec my_proc ''XX'',''YY''

','XX',NAME),'YY',LEFT(NAME,2)) + 'GO' FROM MASTER..SYSDATABASES WITH(NOLOCK)

WHERE DBID>5

Michelle-164472 SSCommitted Points: 1715 More actions · Answer 7

Thanks, however this puts the 'GO' on the SAME line as the sql statement...I want the 'GO' to be placed on a line by itself...

Instead of this:

exec my_proc 'Northwind','No' GO

I want this to be GENERATED:

exec my_proc 'Northwind','No'

GO

exec my_proc 'Pubs','No'

GO

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 8

When I run it the GO is on a separate line. The last version you posted works, also.

Are you running this with results to grid or results to text? It won't appear correctly in grid results, the line breaks get stripped out.

Michelle-164472 SSCommitted Points: 1715 More actions · Answer 9

YOU ARE SOOOO RIGHT!!! I was running via GRID, when switched to TEXT - it display as expected...

Thank you very much...Michelle

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 10

It's important that it print right on the screen? Ok.. I have to ask... what are you actually doing with this? Hopefully you're not doing a cut and paste from the screen, right? If you're trying to get a query to write a bit of code for you, why aren't you using dynamic SQL? What is it that you are actually trying to do? I think you're only telling us part of the story and if you tell the rest, you might be surprised at how much help you can actually get.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Michael Meierruth SSChampion Points: 10051 More actions · Answer 11

What am I missing?

When I run

SELECT REPLACE(REPLACE('exec my_proc ''XX'',''YY''

','XX',NAME),'YY',LEFT(NAME,2)) + 'GO' FROM MASTER..SYSDATABASES WITH(NOLOCK)

WHERE DBID>5

I get

exec my_proc 'Northwind','No'

GO

exec my_proc 'VIEWS','VI'

GO

exec my_proc 'SINSYS','SI'

GO

exec my_proc 'SSB','SS'

GO

exec my_proc 'VRETE','VR'

GO

exec my_proc 'ACLATINA','AC'

GO

When I run

select 'Hey' + 'GO' from master..sysdatabases where dbid>5

I get

HeyGO

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 12

You're missing the implied carriage returns both captured in single quotes and by the return of each line... to emphasize...

SELECT 'Hey

'+'Go'

FROM MASTER..SYSDATABASES WITH(NOLOCK)

WHERE DBID>5

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Michael Meierruth SSChampion Points: 10051 More actions · Answer 13

Didn't know there was such a thing as an implied carriage return. Maybe it's clearer to have an explicit carriage return as in

select 'Hey'+char(13)+'GO' ...

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 14

This question is still not answered... and it really may simplify your work at this point!!!