How to log message using Select query in SQL and Print statement

Sometimes , Its not easy to write log\print messages for DML operations on set of rows in table.


We can use store text in xml using select query and print messages for logging when performing DML operations on set of rows in table

```sql
Declare @NewLine As char(2) = char(13)+ char(10)
		,@empty as char(1)=''
        ,@xmltext as xml,
        ,@CurrentTimp as DateTime =Current_timestamp
        ,@CurrentUser as Varchar(20) =Current_User
        ,@ActivityID =12;
Declare @folios as table ( ID INT Identity(1,1),PortfolioId int, Name Varchar (10) , UserName varchar(15) , SSN Char(9))
      

Insert @folios Select PortfolioId,Name, UserName,SSN,IsActive from Portfolio (nolock) Pf where pf.IsActive=0 and pf.SSN not like '000%'

BEGIN Try

BEGIN Transaction

	Update Portfolio
        Set IsActive =1
        from @folios fol 
        join Portfolio (nolock) Pf on pf.PortfolioId=fol.PortfolioId
      
	if (@@rowcount >0 ) Begin
    		Insert into AuditLog(ActivityID
                        ,Message
                        ,AcivityTimeStamp
                        ,User)
             	  Select @ActivityID       
                        ,'Portfolio -'+pf.Name+ 'has been active for user '+ pf.UserName +' with SSN '+ STUFF(pf.SSN,1,5,'XXX-XX-')
                        , @CurrentTimp
                        , @CurrentUser
                  From @folios

       Select @xmltext =(Select 
       					'Portfolio -'+pf.Name+ 'has been active for user '+ pf.UserName +' with SSN '+ STUFF(pf.SSN,1,5,'XXX-XX-')
                         From @folios [temp]
                         for XML Auto)
		PRINT Replace(Replace(Convert(NVARCHAR(MAX) ,@xmltext),'<temp txt="',@empty),'/<',@NewLine)
                        
	END 
Commit Transaction

END TRY
Begin Catch 
  	Select Error_Number() as ErrorNumber
    	 , Error_Message() as ErrorMessage;
             
   if (XACT_State())=-1
   Begin
    Print N 'The Transaction is in an uncommittable state. Rolling back transaction.'
     Rollback Transaction;
   End
   if (XACT_STATE())=1
   Begin
    PRINT N'The Transaction is committable. Committing transaction.'
     COMMIT Transaction
   End
End Catch
   

```