ONE QUERY OR TWO - URGENT!!

[osjohnm]

Hi all

I have to create a stored proc that will email vendors to confirm the previous days purchase orders.

There are 3 tables:
PURCHASE_HEADER
po_no date_updated vb_id
VENDOR_BRANCH
vb_id vb_name vb_email
USER_MAIL
mail_id mail_to, mail_from, mail_to_address mail_message

Basically it will mail the vendor branch with the count of po's and the po nos. This is then inserted into the USER_MAIL table and gets mailed.

Here is the catch, the mail_id on USER_MAIL is not incremented so I use a cursor to loop thru the records and increment it, the mail_message is hardcorded and I pass the cursor variables into it.

This works fine for the vb_name, vb_email and count of po's

select b.branch_name, b.branch_email, count(ph.po_no) from purchase_header ph, vendor_branch b
where ph.module_code = 'ST'
and datediff(d, ph.date_updated, getdate()) = 1
and ph.vendor_branch = b.vendor_branch_id
group by b.branch_name, b.branch_email, ph.date_updated

How do I "add" the po no's into the mail?
eg;
Good Day'+ @vname+' This is to confirm the '+@pocount' po's which were placed yesterday...po1, po2, po3

Here is my sql to get the po's:
select b.branch_name, b.branch_email, ph.po_no from purchase_header ph, vendor_branch b
where ph.module_code = 'ST'
and datediff(d, ph.date_updated, getdate()) = 1
and ph.vendor_branch = b.vendor_branch_id
group by b.branch_name, b.branch_email, ph.po_no, ph.date_updated

Do I have to add another cursor for the po's and if so how do I pass that back into the insert statment?
or can I somehow get all of this into one?

Any help would be appreciated! I'm on a tight deadline

John

 

[uncLez]

I'm no guru, so there might pop up some better solutions, but I would use the cursor, in a loop fetch your po-no's and have your StorProc build a string using CAST and string concatenation,

then when reaching the end of your cursor, pass that string onto the INSERT statement...

 

[osjohnm]

I found a way to pass the po's into the email.

declare @po varchar(8000)
select @po = ''
select @po = rtrim(@po) + ph.po_no + ','
from purchase_header ph, vendor_branch b
where ph.module_code = 'ST'
and datediff(d, ph.date_updated, getdate()) = 1
and ph.vendor_branch = b.vendor_branch_id
and b.branch_name = @vname

then i just take the message and concatenate it like this
text'+ substring( @po, 1, len( @po) - 1)+ 'text

and it works.

John