email query results

• Jeff-132889

  Ten Centuries

  Points: 1112

  More actions

  November 23, 2005 at 9:31 am

  #90926

  I am building a query that will send 70 emails to users. The content of the email will be from another query that is filtered by the recipients department.

  Currently I am held up with trying to figure out how to get multiple fields into a variable.

  Works fine with one column:

  select @body1=@body1 + char(10) + lastname from tblstaff

   

  As soon as I add a second column it gives me the error:

  A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

  select @body1=@body1 + char(10) + firstname, lastname from tblstaff 

   

  Obviously the error msg is telling me that I can't do it this way and maybe the fact that one column is working is just luck ? How can I assign a result set with multiple columns to a variable?

  Thanks..........

   

   

   

   

• Roy Rogers

  SSCertifiable

  Points: 7712

  More actions

  November 23, 2005 at 12:14 pm

  #605528

  select @body1=@body1 + char(10) + firstname, lastname from tblstaff 

  You are assining a value to varaible @body1. What are you trying to do with lastname.

  Query should be

  select @body1=@body1 + char(10) + firstname + ' ' + lastname from tblstaff 

  Regards,
  gova

• Sameer Raval

  Hall of Fame

  Points: 3214

  More actions

  November 23, 2005 at 12:24 pm

  #605530

  This is how you can resolve your problem

  declare @body1 varchar(100)

  set @body1 = 'Welcome..'

  select top 1 @body1=@body1 + char(10) + au_lname  from pubs..authors

  select @body1

  select top 1 @body1=@body1 + char(10) + au_fname  from pubs..authors

  select @body1

  Result

  Welcome.. Bin

  Welcome.. Bin  Cheryl

  Kindest Regards,

  Sameer Raval [Sql Server DBA]
  Geico Insurance
  RavalSameer@hotmail.com

• Roy Rogers

  SSCertifiable

  Points: 7712

  More actions

  November 23, 2005 at 1:05 pm

  #605543

  Original poster asked to have multiple fields not multiple values of fname. There is no need to make multiple selects. It will work fine with

  select @body1=@body1 + char(10) + firstname + ' ' + lastname + char(10) + CONVERT(VARCHAR, OtherCol1) from tblstaff WHERE Criteria.

  Regards,
  gova

• Jeff-132889

  Ten Centuries

  Points: 1112

  More actions

  November 23, 2005 at 1:21 pm

  #605547

  Thanks Govinn that was exactly what I was looking for. I didn't think of the doing it that way but it's perfect.

  Jeff