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?

Pat Hartman · Sep 21, 2005

I can't tell what you're trying to do but it sounds like you need to use either a sub-select or nested queries. In any event, queries have no way to directly use VBA variables.

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

Pat Hartman · Sep 28, 2005

I would use a regular querydef. You can export that as text, a spreadsheet, or xml with a single command. With a sp, I think you will need to actually open the recordset, read it, and write it out record by record in whatever format you want.

result in to variable

gumster

Guest

Pat Hartman

Super Moderator

NPUser

Registered User.

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread