February 25, 2002 at 4:05 pm
I am retrieving a set of email addresses using a cursor and setting the address equal to a variable. Within the procedure I am using xp_sendmail to email that person. My problem is that I have written an elaborate query that uses the above email address variable to retrieve info. When I set my query equal to @query= and run it, I get a "need to declare variable error" from the query.
If I re-declare the variable inside the query of the xp_sendmail, my original value is erased.
Is there a way to pass the value of the variable, which comes from outside the xp_sendmail procedure into the @query within the xp_sendmail procedure.
February 25, 2002 at 4:55 pm
can you post code?
I think you want to build the query using the value of the variable, not the variable.
so if I want to email people named 'steve' and @name = 'steve'
declare @cmd varchar( 1000)
select @cmd = 'select * from people where name = ''' + @name + ''''
xp_sendmail @recipients = @whoever, @query = @cmd
Steve Jones
February 25, 2002 at 4:57 pm
Can you post a sample of the code you are trying to execute so that we can see exactly why you are getting that error?
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 25, 2002 at 9:11 pm
SAMPLE CODE:
declare @authoremail as varchar(80)
declare @authorhomeemail as varchar(80)
declare emcursor cursor
global
for
select distinct authorid, authoremail from authors
where authorid is not null and authoremail is not null
and authoremail not like '' and authorid not like 'CHANGEME'
order by authorid
open global emcursor
while ( 0 = 0 ) begin
fetch next
from emcursor
into @authoremail, @authorhomeemail <---------VARIABLE VALUES SET
if ( @@fetch_status <> 0 ) break
exec master..xp_sendmail
@recipients=@authoremail, <----------------WORKS NO PROBLEM
@subject='report',
@query='...
select @grosstrans=
count(o.prodid1) from orders o, tests t
where o.total <> '0.00' and o.total <> '0' and o.prodid1=t.productid
and o.orderid like @date1 and o.orderid in (select productid from
tests where testauthor=@authoremail)... <---------DOESN'T WORK, SAYS MUST DECLARE,
DECLARING WILL RESET THE VALUE
This is a very simplified version of my code. I have left one instance, just for example. The error is with the instance
inside the query. I want to pass the variable from the cursor. There is no problem
with using it in the recipients part of xp_sendmail. How can I use in within the query?
Thanks,
Marc
February 26, 2002 at 4:32 am
I think you can fix this by using a temp table. Put all the e-mails in a temp table and then use the temp table in place of:
(select productid from
tests where testauthor=@authoremail)
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 26, 2002 at 7:59 am
Are you certain that @authoremail is the problem in that line? Based on the code provided, I don't see any declartion of @date1. Hope this helps.
February 26, 2002 at 10:40 am
Thanks for the replies. I will try a temp table.
I have removed code for the sake of simplification, that is why some parts are not there.
March 5, 2002 at 3:29 pm
final solution:
I created a stored procedure of the query with an input parameter of @authoremail. This allowed me to set a string variable, @myquery, equal to the execute statement + the @authoremail variable.
Then when I called the xp_sendmail procedure, I just set the @query=@myquery.
create procedure sp_send_royalty_info
as
declare @authoremail as varchar(80)
declare @myquery as varchar(200)
--declare cursor to loop through records in database retrieving author's boson and real email
declare emcursor cursor
global
for
select distinct authorid
from authors a, tests t
where a.authorid is not null and a.authoremail is not null and a.authorpassword is not null
and a.authoremail not like '' and a.authorpassword not like '' and a.authorid not like 'CHANGEME'
and a.authorid=t.testauthor
group by a.authorid
open global emcursor
while ( 0 = 0 ) begin
fetch next
from emcursor
into @authoremail
if ( @@fetch_status <> 0 ) break
set @myquery = 'exec sp_author_monthly' + char(39) + @authoremail + char(39)
exec master..xp_sendmail
@recipients = @authoremail,
@subject = 'HI
@query = @myquery
end
close global emcursor
deallocate emcursor
March 5, 2002 at 3:40 pm
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply