Using Params in the middle of text fields

[dnfrantum]

I am sure that I am missing an escape character, but I can't figure it out. Can someone help me out?

CREATE PROC sp_SendConfirmationMail AS 
   BEGIN  
    SET NOCOUNT ON  
    DECLARE @em nvarchar(100), @order_id nvarchar(100),@rc int  
    DECLARE EmailCursor CURSOR  
     FOR SELECT 'leagueofnerds@hotmail.com'as EM,'102030'as order_id  /*Create Select Statement Here*/
     OPEN EmailCursor  
     FETCH NEXT FROM EmailCursor INTO @em,@order_id
      WHILE @@Fetch_Status = 0 BEGIN    
      EXEC @rc = master.dbo.xp_smtp_sendmail      
      @FROM       = N'customerservice@goorin.com',      
      @TO         = @em,      
      @priority   = N'NORMAL',      
      @subject    = N'Your recent Goorin Order '@order_id N' is being processed.' ,      
      @message    = N'Thank you for your recent order.  We wanted to let you know that we value your business and are currently processing your order for shipment today.',      
      @type       = N'html',      
      @server     = N'mail.goorin.com'    
       SELECT Email = @em, RC = @rc    
        FETCH NEXT FROM YourCursor INTO @em  
      END
     CLOSE EmailCursor  
    DEALLOCATE EmailCursur
   END

Thanks in advance,
Donald

 

[dnfrantum]

I should have pointed out that I am trying to inject @order_id into the middle of a sentence for @message.

Thanks in advance,
Donald

 

[SgtJarrow]

Maybe?:

@subject = N'Your recent Goorin Order ' + @order_id + N' is being processed.' ,

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[dnfrantum]

It doesn't like the '+' signs. I think I have to escape out of the string first or there is a different way of concatenating this.

Thanks in advance,
Donald

 

[SQLBill]

Are you trying to put @order_id in the message (@message) or subject (@subject). Your code shows it in @subject, but you stated in the following post:

I should have pointed out that I am trying to inject @order_id into the middle of a sentence for @message.

I don't think you can concatenate the @subject, but shouldn't have any problems with doing in the @message.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[dnfrantum]

Either should work, no? I am just concatenating a string to a parameter. I think this should work for any NVARCHAR parameter.

In any event, I changed it and it still didn't work. I will keep looking at it.

Thanks in advance,
Donald

 

[SQLSister]

try this:

CREATE PROC sp_SendConfirmationMail 
 AS    
 BEGIN      
 SET NOCOUNT ON      
 DECLARE @em nvarchar(100), @order_id nvarchar(100),@rc int,
 @FROM nvarchar(100), @TO nvarchar(100),  @priority nvarchar (10),
 @subject nvarchar (100),  @message nvarchar(max), @type nvarchar (10),
  @server  nvarchar(100)   
 DECLARE EmailCursor CURSOR       
 FOR 
 SELECT 'leagueofnerds@hotmail.com'as EM,'102030'as order_id  /*Create Select Statement Here*/     
 OPEN EmailCursor       
 FETCH NEXT FROM EmailCursor INTO @em,@order_id      
 WHILE @@Fetch_Status = 0 BEGIN          
 
 SET @FROM       = N'customerservice@goorin.com' 
 SET @TO         = @em
 SET @priority   = N'NORMAL'
 SET @subject    = N'Your recent Goorin Order ' +@order_id +N' is being processed.' 
 SET @message    = N'Thank you for your recent order.  We wanted to let you know that we value your business and are currently processing your order for shipment today.'
 SET @type       = N'html'
 SET @server     = N'mail.goorin.com'                 
 EXEC @rc = master.dbo.xp_smtp_sendmail  @FROM,@TO ,@priority ,  @subject, @message, @type 
 SELECT Email = @em, RC = @rc
 
 FETCH NEXT FROM YourCursor INTO @em        END     
 CLOSE EmailCursor      
 DEALLOCATE EmailCursor   END

Note I didn't look up the actual types the sp would have for variables, but use those in declaring your variables and of course put them inthe correct order in the exec statment.

"NOTHING is more important in a database than integrity." ESquared

 

[markros]

This line

SET @subject = N'Your recent Goorin Order ' +@order_id +N' is being processed.'

should be written as

SET @subject    = N'Your recent Goorin Order ' + cast(@order_id as nvarchar(20)) + N' is being processed.'

PluralSight Learning Library

 

[SQLBill]

The command doesn't like a parameter inside the @subject. However, if markos' suggestion doesn't work, this will...

WHILE @@Fetch_Status = 0 BEGIN

DECLARE @newsubject NVARCHAR(100)
SET @newsubject = N'Your recent Goorin Order + '@order_id + N' is being processed.'
 
 SET @FROM       = N'customerservice@goorin.com' 
 SET @TO         = @em
 SET @priority   = N'NORMAL'
 SET @subject    = @newsubject
 SET @message    = N'Thank you for your recent order.  We wanted to let you know that we value your business and are currently processing your order for shipment today.'
 SET @type       = N'html'
 SET @server     = N'mail.goorin.com'                 
 EXEC @rc = master.dbo.xp_smtp_sendmail  @FROM,@TO ,@priority ,  @subject, @message, @type 
 SELECT Email = @em, RC = @rc

-SQLBill


The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[SQLBill]

This is how I tested the @subject line issue:

use msdb
go
DECLARE @test VARCHAR(10)
SET @test = 'A test from SQlBill'
EXEC msdb.dbo.sp_send_dbmail 
    @recipients = '<emailaddress>',
    @subject = @test,
    @body = 'This is just a test...'

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[markros]

You're right. In addition, I see that no CAST needed as the OrderID is already nvarchar(100) - I assumed it was an integer.

PluralSight Learning Library