result in to variable

gumster · Sep 20, 2005

how can i get the result from a query in to a variable, i want to use this result in a new query?

NPUser · Sep 28, 2005

I am in same suitation - need advice

I am working on a SP to send result of a query via email. If i could save the result of query (@MSG3), then i could attached that as @message message body. Is there better way to do this?

sa

Code:

CREATE procedure olc_id  as
 
Declare @MSG varchar(255),
           @MSG2 varchar(255),
           @MSG3 varchar(4000), 
           @FROM NVARCHAR(4000),
           @FROM_NAME NVARCHAR(4000),
           @TO NVARCHAR(4000), 
           @CC NVARCHAR(4000),
           @BCC NVARCHAR(4000),
           @priority NVARCHAR(10),
           @subject NVARCHAR(4000),
           @message NVARCHAR(4000),
           @type NVARCHAR(100),
           @codepage INT,
           @rc INT
    
   
    
@MSG3 = select convert(char(9),na.id_num) as ID_Num,
            na.last_name,   
            na.first_name  
      FROM name_and_address na 
      WHERE na.id_num = 596   
 


 
 Select    @MSG = "ID NUm ",
           @MSG2 = "Changed ID - Current Date: " + Convert(varchar(200),GetDate(),101),
           @FROM = N'dba@test.com',
           @FROM_NAME = N'dba',
           @TO = N'Somename',
           @CC = N'',
           @BCC = N'',
           @priority = N'High',
           @subject = N'SQL Server SMTP mail',
           @message = N'<HTML><H1>Hello SQL Server SMTP SQL Mail</H1></HTML>' + MSG + MSG2 + MSG3,
           @type = N'text/html',
           @codepage = 0
 
   exec @rc = master.dbo.xp_smtp_sendmail
           @FROM = @FROM,
           @TO = @TO,
           @CC = @CC,
           @BCC = @BCC,
           @priority = @priority,
           @subject = @subject,
           @message = @message,
           @type = @type,
           @codepage = @codepage,
           @server = N'192.168.1.3'
   
select RC = @rc
    
GO

result in to variable

gumster

Guest

NPUser

Registered User.

Similar threads

Users who are viewing this thread