Search code examples
sqlsql-serversql-server-2017

Concatenate a parameter value with a string


I am using SQL Server 2017. I have code that is working well for what I am applying it to. However, I would like to send the parameter value with some text/string. I have tried many formats to combine or concatenate the two. I have tried this
@body = @AssociateTaskID + 'you have been assigned this id'; also tried this @body = @AssociateTaskID + + 'you have been assigned this id'; I get syntax errors on both. Is there any way to do this and make it work? If so, what's the syntax?

   Begin
      declare @AssociateTaskID int;
      declare @TaskAction_A1_assigned varchar(50);
      declare @TaskAction_A1_started varchar(100);
      declare @TaskAsction_A1_completed varchar(100);

      select @AssociateTaskID = s.AssociateTaskID from inserted s;
      select @TaskAction_A1_assigned = s.TaskAction_A1_assigned from inserted s;
  select @TaskAction_A1_started = s.TaskAction_A1_started from inserted s;
      select @TaskAsction_A1_completed = s.TaskAsction_A1_completed from inserted s;


   ------Send notification to TaskAction_A1_owner
     
    if update(TaskAction_A1_assigned)

      EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'Echo System',
      @recipients = '[email protected]',
      @subject = 'Echo System Notification',
      @body = @AssociateTaskID;

Solution

  • The concatenation could be assigned to a variable. Something like this

          declare 
            @AssociateTaskID            int,
            @TaskAction_A1_assigned     varchar(50),
            @TaskAction_A1_started      varchar(100),
            @TaskAsction_A1_completed   varchar(100),
            @Message                    nvarchar(4000);
    
          select @AssociateTaskID = s.AssociateTaskID,
                 @TaskAction_A1_assigned = s.TaskAction_A1_assigned,
                 @TaskAction_A1_started = s.TaskAction_A1_started,
                 @TaskAsction_A1_completed = s.TaskAsction_A1_completed
          from inserted s;
    
          select @Message=concat(cast(@AssociateTaskID as nvarchar(12)), N'you have been assigned this id');
         
        --if update(TaskAction_A1_assigned)
    
          EXEC msdb.dbo.sp_send_dbmail
          @profile_name = 'Echo System',
          @recipients = '[email protected]',
          @subject = 'Echo System Notification',
          @body = @Message;