xp_sendmail @query

• thundr51

  Old Hand

  Points: 313

  More actions

  November 19, 2003 at 8:52 am

  #83560

  I'm trying to send an email with a query but I need

  to send a parameter in the query.

  
  
  	exec master.dbo.xp_sendmail
  
  	@recipients=@mail,
  
  	@message=@body,
  
  	@subject='E&O Coverage',
  
  	@width=175,
  
  	@query=exec GetEORepsByRA @ID 
  
  
  
  

  @ID has been defined (I cut out all the declarations) an is an integer

  If I try

  @query='exec GetEORepsByRA' + @ID

  I get an error on the '+'

  does this mean that you can not send a query that takes no parameters

  or am I just doing something wrong?

• Steve Jones - SSC Editor

  SSC Guru

  Points: 728159

  More actions

  November 19, 2003 at 9:56 am

  #482692

  set @cmd = 'exec GetEORepsByRA' + cast(@ID as char)

  exec xp_sendmail ....., @query=@cmd

  Steve Jones

  sjones@sqlservercentral.com

  http://qa.sqlservercentral.com/columnists/sjones

  The Best of SQL Server Central.com 2002 - http://qa.sqlservercentral.com/bestof/

  http://www.dkranch.net

  Follow me on Twitter: http://www.twitter.com/way0utwest
  Forum Etiquette: How to post data/code on a forum to get the best help
  My Blog: www.voiceofthedba.com

• ThomasH

  SSCertifiable

  Points: 7437

  More actions

  November 19, 2003 at 9:58 am

  #482694

  Try

  
  
  
  
  DECLARE @QuerySQL Varchar(200)
  
  SET @QuerySQL = 'exec GetEORepsByRA ' + Convert(Varchar(10), @ID)
  
  -- then
  
  	exec master.dbo.xp_sendmail	
  
  	@recipients=@mail,
  
  	@message=@body,
  
  	@subject='E&O Coverage',
  
  	@width=175,
  
  	@query=@QuerySQL
  
  

  Observation: Parameters of PROCs, or for that matter Functions, can not be expressions, they must be eiterh constants or variables.

  
  
  Once you understand the BITs, all the pieces come together

• Chuck Ritenour

  SSC-Addicted

  Points: 420

  More actions

  November 20, 2003 at 6:15 am

  #482837

  And for the plain simple, easy to understand explanation that you can use - the @id was/is returning an INT, and the + is trying to add it, by using the convert function as ThomasH suggests SQL Server understands you want to concatenate because everyone knows you can't add two words together.

  It always pays to understand why, not just what to do...

  Thanks, and don't forget to Chuckle

  
  Thanks, and don't forget to Chuckle