Create stored procedure that contains newlines with a oneliner sql statement





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty{ margin-bottom:0;
}






up vote
2
down vote

favorite












I want to put code in a single line that will create a stored procedure that contains newlines.




  • Is that possible?

  • Do I need to use sp_executesql?

  • How do I escape newlines in a sql statement?

  • How do I escape newlines in a string?










share|improve this question
























  • So you need to include carriage return and new line characters in your stored procedure, but not in your CREATE statement?
    – George.Palacios
    Nov 21 at 13:22










  • Yes, that is true.
    – Anders Lindén
    Nov 21 at 13:23






  • 2




    May we ask why?
    – user1008646
    Nov 21 at 14:33










  • To get one command per line resulting in a cleaner file.
    – Anders Lindén
    Nov 22 at 20:33

















up vote
2
down vote

favorite












I want to put code in a single line that will create a stored procedure that contains newlines.




  • Is that possible?

  • Do I need to use sp_executesql?

  • How do I escape newlines in a sql statement?

  • How do I escape newlines in a string?










share|improve this question
























  • So you need to include carriage return and new line characters in your stored procedure, but not in your CREATE statement?
    – George.Palacios
    Nov 21 at 13:22










  • Yes, that is true.
    – Anders Lindén
    Nov 21 at 13:23






  • 2




    May we ask why?
    – user1008646
    Nov 21 at 14:33










  • To get one command per line resulting in a cleaner file.
    – Anders Lindén
    Nov 22 at 20:33













up vote
2
down vote

favorite









up vote
2
down vote

favorite











I want to put code in a single line that will create a stored procedure that contains newlines.




  • Is that possible?

  • Do I need to use sp_executesql?

  • How do I escape newlines in a sql statement?

  • How do I escape newlines in a string?










share|improve this question















I want to put code in a single line that will create a stored procedure that contains newlines.




  • Is that possible?

  • Do I need to use sp_executesql?

  • How do I escape newlines in a sql statement?

  • How do I escape newlines in a string?







sql-server t-sql stored-procedures dynamic-sql






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 22 at 19:55









Solomon Rutzky

47k579169




47k579169










asked Nov 21 at 13:13









Anders Lindén

2211312




2211312












  • So you need to include carriage return and new line characters in your stored procedure, but not in your CREATE statement?
    – George.Palacios
    Nov 21 at 13:22










  • Yes, that is true.
    – Anders Lindén
    Nov 21 at 13:23






  • 2




    May we ask why?
    – user1008646
    Nov 21 at 14:33










  • To get one command per line resulting in a cleaner file.
    – Anders Lindén
    Nov 22 at 20:33


















  • So you need to include carriage return and new line characters in your stored procedure, but not in your CREATE statement?
    – George.Palacios
    Nov 21 at 13:22










  • Yes, that is true.
    – Anders Lindén
    Nov 21 at 13:23






  • 2




    May we ask why?
    – user1008646
    Nov 21 at 14:33










  • To get one command per line resulting in a cleaner file.
    – Anders Lindén
    Nov 22 at 20:33
















So you need to include carriage return and new line characters in your stored procedure, but not in your CREATE statement?
– George.Palacios
Nov 21 at 13:22




So you need to include carriage return and new line characters in your stored procedure, but not in your CREATE statement?
– George.Palacios
Nov 21 at 13:22












Yes, that is true.
– Anders Lindén
Nov 21 at 13:23




Yes, that is true.
– Anders Lindén
Nov 21 at 13:23




2




2




May we ask why?
– user1008646
Nov 21 at 14:33




May we ask why?
– user1008646
Nov 21 at 14:33












To get one command per line resulting in a cleaner file.
– Anders Lindén
Nov 22 at 20:33




To get one command per line resulting in a cleaner file.
– Anders Lindén
Nov 22 at 20:33










2 Answers
2






active

oldest

votes

















up vote
6
down vote













Yes, you can do something like this with dynamic SQL:



DECLARE @SQL NVARCHAR(MAX) = N'';
DECLARE @NewLine NCHAR(1) = NCHAR(10);

SET @SQL = @SQL + N'SELECT * ' + @NewLine + N'FROM sys.databases AS d ' + @NewLine + N'WHERE d.database_id > 4;' + @NewLine;

PRINT @SQL;
EXEC sys.sp_executesql @SQL;





share|improve this answer





















  • @AndersLindén yep, don't put a + before EXEC.
    – sp_BlitzErik
    Nov 21 at 13:46










  • I was trying to put the + in the argument to exec, building the sql string there
    – Anders Lindén
    Nov 21 at 13:47






  • 1




    @Anders um, why?
    – Aaron Bertrand
    Nov 21 at 13:56










  • The parameter of sp_executesql must be either a variable or a constant. No expressions allowed.
    – user1008646
    Nov 21 at 14:28










  • Is there no way of escaping characters in a t-sql string?
    – Anders Lindén
    Nov 21 at 15:16


















up vote
0
down vote













No, T-SQL cannot escape newlines, tabs, etc. It only has an escape sequence for embedded string delimiters: '' = '. There is also an escape sequence for delimited identifiers: either "" for ", or ]] for ], depending on which one is being used to delimit the identifier.



If you want to work with escape sequences, you can do so manually via the REPLACE function.



Depending on how the "single line of code" is being interpreted, you can do either:



IF (OBJECT_ID(N'tempdb..#TestProc') IS NOT NULL)
BEGIN
DROP PROCEDURE #TestProc;
END;
GO

-- The following is technically a single line (with multiple commands):
DECLARE @SQL NVARCHAR(MAX) = N'CREATE PROCEDURE #TestProcn(n @Param1 INT,n @Param2 NVARCHAR(128)n)nASnSET NOCOUNT ON;nnSELECT *nFROM tempdb.sys.sql_modulesnWHERE [object_id] = OBJECT_ID(N''tempdb..'' + @Param2);'; SET @SQL = REPLACE(@SQL, N'n', NCHAR(10)); EXEC (@SQL);

-- View the definition:
PRINT @SQL;

-- Test the proc:
EXEC #TestProc 2, N'#TestProc';


Or, if you can only execute a single command/statement, then you can try the following:



IF (OBJECT_ID(N'tempdb..#TestProc2') IS NOT NULL)
BEGIN
DROP PROCEDURE #TestProc2;
END;
GO

-- The following is a single line and a single statement:
EXEC (N'DECLARE @SQL NVARCHAR(MAX) = N''CREATE PROCEDURE #TestProc2n(n @Param1 INT,n @Param2 NVARCHAR(128)n)nASnSET NOCOUNT ON;nnSELECT *nFROM tempdb.sys.sql_modulesnWHERE [object_id] = OBJECT_ID(N''''tempdb..'''' + @Param2);''; SET @SQL = REPLACE(@SQL, N''n'', NCHAR(10)); EXEC (@SQL);');

-- Test the proc:
EXEC #TestProc2 2, N'#TestProc2';

-- View the definition:
DECLARE @SQL2 NVARCHAR(MAX);
SELECT @SQL2 = [definition]
FROM tempdb.sys.sql_modules
WHERE [object_id] = OBJECT_ID(N'tempdb..#TestProc2');

PRINT @SQL2;
-- returns (in "Messages" tab):
/*
CREATE PROCEDURE #TestProc2
(
@Param1 INT,
@Param2 NVARCHAR(128)
)
AS
SET NOCOUNT ON;

SELECT *
FROM tempdb.sys.sql_modules
WHERE [object_id] = OBJECT_ID(N'tempdb..' + @Param2);
*/





share|improve this answer





















    Your Answer








    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "182"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    convertImagesToLinks: false,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: null,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














     

    draft saved


    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f223095%2fcreate-stored-procedure-that-contains-newlines-with-a-oneliner-sql-statement%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    2 Answers
    2






    active

    oldest

    votes








    2 Answers
    2






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes








    up vote
    6
    down vote













    Yes, you can do something like this with dynamic SQL:



    DECLARE @SQL NVARCHAR(MAX) = N'';
    DECLARE @NewLine NCHAR(1) = NCHAR(10);

    SET @SQL = @SQL + N'SELECT * ' + @NewLine + N'FROM sys.databases AS d ' + @NewLine + N'WHERE d.database_id > 4;' + @NewLine;

    PRINT @SQL;
    EXEC sys.sp_executesql @SQL;





    share|improve this answer





















    • @AndersLindén yep, don't put a + before EXEC.
      – sp_BlitzErik
      Nov 21 at 13:46










    • I was trying to put the + in the argument to exec, building the sql string there
      – Anders Lindén
      Nov 21 at 13:47






    • 1




      @Anders um, why?
      – Aaron Bertrand
      Nov 21 at 13:56










    • The parameter of sp_executesql must be either a variable or a constant. No expressions allowed.
      – user1008646
      Nov 21 at 14:28










    • Is there no way of escaping characters in a t-sql string?
      – Anders Lindén
      Nov 21 at 15:16















    up vote
    6
    down vote













    Yes, you can do something like this with dynamic SQL:



    DECLARE @SQL NVARCHAR(MAX) = N'';
    DECLARE @NewLine NCHAR(1) = NCHAR(10);

    SET @SQL = @SQL + N'SELECT * ' + @NewLine + N'FROM sys.databases AS d ' + @NewLine + N'WHERE d.database_id > 4;' + @NewLine;

    PRINT @SQL;
    EXEC sys.sp_executesql @SQL;





    share|improve this answer





















    • @AndersLindén yep, don't put a + before EXEC.
      – sp_BlitzErik
      Nov 21 at 13:46










    • I was trying to put the + in the argument to exec, building the sql string there
      – Anders Lindén
      Nov 21 at 13:47






    • 1




      @Anders um, why?
      – Aaron Bertrand
      Nov 21 at 13:56










    • The parameter of sp_executesql must be either a variable or a constant. No expressions allowed.
      – user1008646
      Nov 21 at 14:28










    • Is there no way of escaping characters in a t-sql string?
      – Anders Lindén
      Nov 21 at 15:16













    up vote
    6
    down vote










    up vote
    6
    down vote









    Yes, you can do something like this with dynamic SQL:



    DECLARE @SQL NVARCHAR(MAX) = N'';
    DECLARE @NewLine NCHAR(1) = NCHAR(10);

    SET @SQL = @SQL + N'SELECT * ' + @NewLine + N'FROM sys.databases AS d ' + @NewLine + N'WHERE d.database_id > 4;' + @NewLine;

    PRINT @SQL;
    EXEC sys.sp_executesql @SQL;





    share|improve this answer












    Yes, you can do something like this with dynamic SQL:



    DECLARE @SQL NVARCHAR(MAX) = N'';
    DECLARE @NewLine NCHAR(1) = NCHAR(10);

    SET @SQL = @SQL + N'SELECT * ' + @NewLine + N'FROM sys.databases AS d ' + @NewLine + N'WHERE d.database_id > 4;' + @NewLine;

    PRINT @SQL;
    EXEC sys.sp_executesql @SQL;






    share|improve this answer












    share|improve this answer



    share|improve this answer










    answered Nov 21 at 13:25









    sp_BlitzErik

    20.6k1262102




    20.6k1262102












    • @AndersLindén yep, don't put a + before EXEC.
      – sp_BlitzErik
      Nov 21 at 13:46










    • I was trying to put the + in the argument to exec, building the sql string there
      – Anders Lindén
      Nov 21 at 13:47






    • 1




      @Anders um, why?
      – Aaron Bertrand
      Nov 21 at 13:56










    • The parameter of sp_executesql must be either a variable or a constant. No expressions allowed.
      – user1008646
      Nov 21 at 14:28










    • Is there no way of escaping characters in a t-sql string?
      – Anders Lindén
      Nov 21 at 15:16


















    • @AndersLindén yep, don't put a + before EXEC.
      – sp_BlitzErik
      Nov 21 at 13:46










    • I was trying to put the + in the argument to exec, building the sql string there
      – Anders Lindén
      Nov 21 at 13:47






    • 1




      @Anders um, why?
      – Aaron Bertrand
      Nov 21 at 13:56










    • The parameter of sp_executesql must be either a variable or a constant. No expressions allowed.
      – user1008646
      Nov 21 at 14:28










    • Is there no way of escaping characters in a t-sql string?
      – Anders Lindén
      Nov 21 at 15:16
















    @AndersLindén yep, don't put a + before EXEC.
    – sp_BlitzErik
    Nov 21 at 13:46




    @AndersLindén yep, don't put a + before EXEC.
    – sp_BlitzErik
    Nov 21 at 13:46












    I was trying to put the + in the argument to exec, building the sql string there
    – Anders Lindén
    Nov 21 at 13:47




    I was trying to put the + in the argument to exec, building the sql string there
    – Anders Lindén
    Nov 21 at 13:47




    1




    1




    @Anders um, why?
    – Aaron Bertrand
    Nov 21 at 13:56




    @Anders um, why?
    – Aaron Bertrand
    Nov 21 at 13:56












    The parameter of sp_executesql must be either a variable or a constant. No expressions allowed.
    – user1008646
    Nov 21 at 14:28




    The parameter of sp_executesql must be either a variable or a constant. No expressions allowed.
    – user1008646
    Nov 21 at 14:28












    Is there no way of escaping characters in a t-sql string?
    – Anders Lindén
    Nov 21 at 15:16




    Is there no way of escaping characters in a t-sql string?
    – Anders Lindén
    Nov 21 at 15:16












    up vote
    0
    down vote













    No, T-SQL cannot escape newlines, tabs, etc. It only has an escape sequence for embedded string delimiters: '' = '. There is also an escape sequence for delimited identifiers: either "" for ", or ]] for ], depending on which one is being used to delimit the identifier.



    If you want to work with escape sequences, you can do so manually via the REPLACE function.



    Depending on how the "single line of code" is being interpreted, you can do either:



    IF (OBJECT_ID(N'tempdb..#TestProc') IS NOT NULL)
    BEGIN
    DROP PROCEDURE #TestProc;
    END;
    GO

    -- The following is technically a single line (with multiple commands):
    DECLARE @SQL NVARCHAR(MAX) = N'CREATE PROCEDURE #TestProcn(n @Param1 INT,n @Param2 NVARCHAR(128)n)nASnSET NOCOUNT ON;nnSELECT *nFROM tempdb.sys.sql_modulesnWHERE [object_id] = OBJECT_ID(N''tempdb..'' + @Param2);'; SET @SQL = REPLACE(@SQL, N'n', NCHAR(10)); EXEC (@SQL);

    -- View the definition:
    PRINT @SQL;

    -- Test the proc:
    EXEC #TestProc 2, N'#TestProc';


    Or, if you can only execute a single command/statement, then you can try the following:



    IF (OBJECT_ID(N'tempdb..#TestProc2') IS NOT NULL)
    BEGIN
    DROP PROCEDURE #TestProc2;
    END;
    GO

    -- The following is a single line and a single statement:
    EXEC (N'DECLARE @SQL NVARCHAR(MAX) = N''CREATE PROCEDURE #TestProc2n(n @Param1 INT,n @Param2 NVARCHAR(128)n)nASnSET NOCOUNT ON;nnSELECT *nFROM tempdb.sys.sql_modulesnWHERE [object_id] = OBJECT_ID(N''''tempdb..'''' + @Param2);''; SET @SQL = REPLACE(@SQL, N''n'', NCHAR(10)); EXEC (@SQL);');

    -- Test the proc:
    EXEC #TestProc2 2, N'#TestProc2';

    -- View the definition:
    DECLARE @SQL2 NVARCHAR(MAX);
    SELECT @SQL2 = [definition]
    FROM tempdb.sys.sql_modules
    WHERE [object_id] = OBJECT_ID(N'tempdb..#TestProc2');

    PRINT @SQL2;
    -- returns (in "Messages" tab):
    /*
    CREATE PROCEDURE #TestProc2
    (
    @Param1 INT,
    @Param2 NVARCHAR(128)
    )
    AS
    SET NOCOUNT ON;

    SELECT *
    FROM tempdb.sys.sql_modules
    WHERE [object_id] = OBJECT_ID(N'tempdb..' + @Param2);
    */





    share|improve this answer

























      up vote
      0
      down vote













      No, T-SQL cannot escape newlines, tabs, etc. It only has an escape sequence for embedded string delimiters: '' = '. There is also an escape sequence for delimited identifiers: either "" for ", or ]] for ], depending on which one is being used to delimit the identifier.



      If you want to work with escape sequences, you can do so manually via the REPLACE function.



      Depending on how the "single line of code" is being interpreted, you can do either:



      IF (OBJECT_ID(N'tempdb..#TestProc') IS NOT NULL)
      BEGIN
      DROP PROCEDURE #TestProc;
      END;
      GO

      -- The following is technically a single line (with multiple commands):
      DECLARE @SQL NVARCHAR(MAX) = N'CREATE PROCEDURE #TestProcn(n @Param1 INT,n @Param2 NVARCHAR(128)n)nASnSET NOCOUNT ON;nnSELECT *nFROM tempdb.sys.sql_modulesnWHERE [object_id] = OBJECT_ID(N''tempdb..'' + @Param2);'; SET @SQL = REPLACE(@SQL, N'n', NCHAR(10)); EXEC (@SQL);

      -- View the definition:
      PRINT @SQL;

      -- Test the proc:
      EXEC #TestProc 2, N'#TestProc';


      Or, if you can only execute a single command/statement, then you can try the following:



      IF (OBJECT_ID(N'tempdb..#TestProc2') IS NOT NULL)
      BEGIN
      DROP PROCEDURE #TestProc2;
      END;
      GO

      -- The following is a single line and a single statement:
      EXEC (N'DECLARE @SQL NVARCHAR(MAX) = N''CREATE PROCEDURE #TestProc2n(n @Param1 INT,n @Param2 NVARCHAR(128)n)nASnSET NOCOUNT ON;nnSELECT *nFROM tempdb.sys.sql_modulesnWHERE [object_id] = OBJECT_ID(N''''tempdb..'''' + @Param2);''; SET @SQL = REPLACE(@SQL, N''n'', NCHAR(10)); EXEC (@SQL);');

      -- Test the proc:
      EXEC #TestProc2 2, N'#TestProc2';

      -- View the definition:
      DECLARE @SQL2 NVARCHAR(MAX);
      SELECT @SQL2 = [definition]
      FROM tempdb.sys.sql_modules
      WHERE [object_id] = OBJECT_ID(N'tempdb..#TestProc2');

      PRINT @SQL2;
      -- returns (in "Messages" tab):
      /*
      CREATE PROCEDURE #TestProc2
      (
      @Param1 INT,
      @Param2 NVARCHAR(128)
      )
      AS
      SET NOCOUNT ON;

      SELECT *
      FROM tempdb.sys.sql_modules
      WHERE [object_id] = OBJECT_ID(N'tempdb..' + @Param2);
      */





      share|improve this answer























        up vote
        0
        down vote










        up vote
        0
        down vote









        No, T-SQL cannot escape newlines, tabs, etc. It only has an escape sequence for embedded string delimiters: '' = '. There is also an escape sequence for delimited identifiers: either "" for ", or ]] for ], depending on which one is being used to delimit the identifier.



        If you want to work with escape sequences, you can do so manually via the REPLACE function.



        Depending on how the "single line of code" is being interpreted, you can do either:



        IF (OBJECT_ID(N'tempdb..#TestProc') IS NOT NULL)
        BEGIN
        DROP PROCEDURE #TestProc;
        END;
        GO

        -- The following is technically a single line (with multiple commands):
        DECLARE @SQL NVARCHAR(MAX) = N'CREATE PROCEDURE #TestProcn(n @Param1 INT,n @Param2 NVARCHAR(128)n)nASnSET NOCOUNT ON;nnSELECT *nFROM tempdb.sys.sql_modulesnWHERE [object_id] = OBJECT_ID(N''tempdb..'' + @Param2);'; SET @SQL = REPLACE(@SQL, N'n', NCHAR(10)); EXEC (@SQL);

        -- View the definition:
        PRINT @SQL;

        -- Test the proc:
        EXEC #TestProc 2, N'#TestProc';


        Or, if you can only execute a single command/statement, then you can try the following:



        IF (OBJECT_ID(N'tempdb..#TestProc2') IS NOT NULL)
        BEGIN
        DROP PROCEDURE #TestProc2;
        END;
        GO

        -- The following is a single line and a single statement:
        EXEC (N'DECLARE @SQL NVARCHAR(MAX) = N''CREATE PROCEDURE #TestProc2n(n @Param1 INT,n @Param2 NVARCHAR(128)n)nASnSET NOCOUNT ON;nnSELECT *nFROM tempdb.sys.sql_modulesnWHERE [object_id] = OBJECT_ID(N''''tempdb..'''' + @Param2);''; SET @SQL = REPLACE(@SQL, N''n'', NCHAR(10)); EXEC (@SQL);');

        -- Test the proc:
        EXEC #TestProc2 2, N'#TestProc2';

        -- View the definition:
        DECLARE @SQL2 NVARCHAR(MAX);
        SELECT @SQL2 = [definition]
        FROM tempdb.sys.sql_modules
        WHERE [object_id] = OBJECT_ID(N'tempdb..#TestProc2');

        PRINT @SQL2;
        -- returns (in "Messages" tab):
        /*
        CREATE PROCEDURE #TestProc2
        (
        @Param1 INT,
        @Param2 NVARCHAR(128)
        )
        AS
        SET NOCOUNT ON;

        SELECT *
        FROM tempdb.sys.sql_modules
        WHERE [object_id] = OBJECT_ID(N'tempdb..' + @Param2);
        */





        share|improve this answer












        No, T-SQL cannot escape newlines, tabs, etc. It only has an escape sequence for embedded string delimiters: '' = '. There is also an escape sequence for delimited identifiers: either "" for ", or ]] for ], depending on which one is being used to delimit the identifier.



        If you want to work with escape sequences, you can do so manually via the REPLACE function.



        Depending on how the "single line of code" is being interpreted, you can do either:



        IF (OBJECT_ID(N'tempdb..#TestProc') IS NOT NULL)
        BEGIN
        DROP PROCEDURE #TestProc;
        END;
        GO

        -- The following is technically a single line (with multiple commands):
        DECLARE @SQL NVARCHAR(MAX) = N'CREATE PROCEDURE #TestProcn(n @Param1 INT,n @Param2 NVARCHAR(128)n)nASnSET NOCOUNT ON;nnSELECT *nFROM tempdb.sys.sql_modulesnWHERE [object_id] = OBJECT_ID(N''tempdb..'' + @Param2);'; SET @SQL = REPLACE(@SQL, N'n', NCHAR(10)); EXEC (@SQL);

        -- View the definition:
        PRINT @SQL;

        -- Test the proc:
        EXEC #TestProc 2, N'#TestProc';


        Or, if you can only execute a single command/statement, then you can try the following:



        IF (OBJECT_ID(N'tempdb..#TestProc2') IS NOT NULL)
        BEGIN
        DROP PROCEDURE #TestProc2;
        END;
        GO

        -- The following is a single line and a single statement:
        EXEC (N'DECLARE @SQL NVARCHAR(MAX) = N''CREATE PROCEDURE #TestProc2n(n @Param1 INT,n @Param2 NVARCHAR(128)n)nASnSET NOCOUNT ON;nnSELECT *nFROM tempdb.sys.sql_modulesnWHERE [object_id] = OBJECT_ID(N''''tempdb..'''' + @Param2);''; SET @SQL = REPLACE(@SQL, N''n'', NCHAR(10)); EXEC (@SQL);');

        -- Test the proc:
        EXEC #TestProc2 2, N'#TestProc2';

        -- View the definition:
        DECLARE @SQL2 NVARCHAR(MAX);
        SELECT @SQL2 = [definition]
        FROM tempdb.sys.sql_modules
        WHERE [object_id] = OBJECT_ID(N'tempdb..#TestProc2');

        PRINT @SQL2;
        -- returns (in "Messages" tab):
        /*
        CREATE PROCEDURE #TestProc2
        (
        @Param1 INT,
        @Param2 NVARCHAR(128)
        )
        AS
        SET NOCOUNT ON;

        SELECT *
        FROM tempdb.sys.sql_modules
        WHERE [object_id] = OBJECT_ID(N'tempdb..' + @Param2);
        */






        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Nov 22 at 19:53









        Solomon Rutzky

        47k579169




        47k579169






























             

            draft saved


            draft discarded



















































             


            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f223095%2fcreate-stored-procedure-that-contains-newlines-with-a-oneliner-sql-statement%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            Plaza Victoria

            Puebla de Zaragoza

            Musa