none
New Line and Carriage return lost when string variable is passed to Stored procedure

    Question

  • Hi,

    We are using SQL Server 2005. I have some data in my table/column. I need to get as string and pass it to email stored procedure which will be used as email body. But when the data is passed, format is lost (newline and carriage return). This is how i did.

    STEP 1:

    Create stored procedure (in my case it is sending email), but here I just use test SP.

    CREATE PROCEDURE [dbo].[usptest]

    @pEmailBody varchar(2000) AS

    BEGIN

    SET NOCOUNT ON;

    select @pEmailBody

    END

    STEP 2:

    declare @t1 table

    (  id int,

       emailbody varchar(1000)

    )

     

    declare @message varchar(1000)

     

    insert into @t1

    select 1, 'This is Correct'

    UNION

    Select 2, 'New Line Feed'

    UNION

    Select 3, 'Next message please'

     

    --select * from @t1

    set @message = '';

    select @message = @message + emailbody + CHAR(10)

    from @t1

    --print @message (if you look at this by uncommenting, you see that data comes on 3 different lines

    EXEC [dbo].[usptest] @message;

    When this stored procedure is run, data shows up in one line loosing format.Please let me know how to handle it. I even tried CHAR(13) and using both also.

    Thanks,

    Spunny

     

     

     

    Friday, September 16, 2011 2:46 PM

Answers

  • Which format the e-mail is sent? You may try using CRLF (CHAR(13) + CHAR(10) + CHAR(13) + char(10)) or if the e-mail is set in HTML format you need to use <br/><br/>
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by Spunny Monday, September 19, 2011 2:34 PM
    Friday, September 16, 2011 3:00 PM
  • Many email programs like Outlook delete multiple CR/LF combinations.

    If you need the email in a specific format, you need to use HTML and use <BR> for line feeds.

    • Marked as answer by Spunny Monday, September 19, 2011 2:35 PM
    Friday, September 16, 2011 3:55 PM

All replies

  • The CHAR(10) character is there, but you can not see it if you output results into a grid. Change Query/Results to text and re-test.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Friday, September 16, 2011 2:52 PM
  • Thanks Naomi for quick reply. I just used stored procedure here to show how it look. The email I get also looses format and all data is coming in one line in email (body).

    Thanks,

    Spuuny

    Friday, September 16, 2011 2:56 PM
  • Which format the e-mail is sent? You may try using CRLF (CHAR(13) + CHAR(10) + CHAR(13) + char(10)) or if the e-mail is set in HTML format you need to use <br/><br/>
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by Spunny Monday, September 19, 2011 2:34 PM
    Friday, September 16, 2011 3:00 PM
  • I tried your above suggestion and got a error saying that 

    'CRLF' is not a recognized built-in function name

    And I tried <br>. What happening is literally <br> is coming as same in body because it is part of string.

    Thanks,

    Spunny

    Friday, September 16, 2011 3:09 PM
  • Noami has already posted what you need as correct line break.

    BUT:

    DECLARE @t1 TABLE (id INT, emailbody NVARCHAR(MAX)) ;
    
    INSERT INTO @t1 VALUES
            ( 1,'This is Correct' ) ,
            ( 2,'New Line Feed' ) ,
            ( 3,'Next message please' );
    
    DECLARE @message NVARCHAR(MAX) ;
    
    SELECT @message = COALESCE(@message + CHAR(13) + CHAR(10), '') + emailbody
    FROM @t1
    ORDER BY id DESC ;
    
    PRINT @message ;

    has an inherent problem. You can provide an ORDER BY to your SELECT for generating your e-mail body. But the using this special syntax (SELECT @var = @var + column) does not guarantee that the ORDER BY is used. So you may get the wrong order of your lines.


    Friday, September 16, 2011 3:11 PM
  • Email is set up in plain text format. Sorry Stefan. I tried your code also. It didn't work in email. The email is sent from back end using

    msdb

    .dbo.sp_send_dbmail

    Thanks,

    Spunny

    Friday, September 16, 2011 3:32 PM
  • How did you know that the CrLf is lost? Using SELECT @emailbody ; does not work in the grid view as Naomi already wrote.

    btw, debug msdb.dbo.sp_send_dbmail and take a look at

    SELECT body
    FROM     msdb..sysmail_mailitems
    WHERE    mailitem_id = @mailitem_id ;

    before the mail item is placed in the queue (line ~465).

    Friday, September 16, 2011 3:42 PM
  • Take a look here

    http://vb-helper.com/howto_shell_mail.html

    There is a possibility that instead of CRLF we need to use these characters.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Friday, September 16, 2011 3:43 PM
  • Many email programs like Outlook delete multiple CR/LF combinations.

    If you need the email in a specific format, you need to use HTML and use <BR> for line feeds.

    • Marked as answer by Spunny Monday, September 19, 2011 2:35 PM
    Friday, September 16, 2011 3:55 PM
  • Stefan,

    CRLF won't work on sql server side. When I am building select query if I use this

    select @message = @message + emailbody + it is coming back as 'CRLF is not a built in function' in SSMS query analyzer. If I put single quoes around it, it is treated as string literal.

    CRLF (CHAR(13) + CHAR(10) + CHAR(13) + char(10))

     

    Thanks,

    Spunny

    Friday, September 16, 2011 4:20 PM
  • Spunny,

    There is no built-in CLRF function. By CLRF we mean CHAR(13) + CHAR(10) and this is what you need to use or do

    declare @CLRF char(2)

    set @CLRF = CHAR(13) + CHAR(10)

    and use this variable.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Friday, September 16, 2011 4:24 PM
  • I tried as suggested by all. But I couldn't make it to work. So, I tried other method suggested in the group as 'HTML'. I created the email in HTML format and set the bodyformat parameter of dbmail to 'HTML'. With this setting,  mail is sent as I expected.

    Thanks,

    Spunny

    Monday, September 19, 2011 2:37 PM