Adding a Carriage Return to sql statement???

  • 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

  • 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)

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

    vbcrlf in vb

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

  • 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'

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

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

  • 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.

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

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

    WHERE DBID>5

  • 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

  • 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.

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

    Thank you very much...Michelle

  • 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)

  • 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

    HeyGO

    HeyGO

    HeyGO

    HeyGO

    HeyGO

     

  • 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)

  • 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' ...

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

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

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