creating stored procedure for emailing

[Ofina]

In SQL 2000, I'm trying to create a stored procedure that will email me the results of the following. The single quotes within the select statement are causing problems. What can I do?

set @query=
'select
P21Play.dbo.fk_temp_new_items.inv_mast_uid,P21Play.dbo.fk_temp_new_items.item_id,
P21Play.dbo.fk_temp_new_items.item_desc+' '+isnull(P21Play.dbo.fk_temp_new_items.extd_desc,''),
P21Play.dbo.fk_temp_new_items.date_created,P21Play.dbo.fk_temp_new_items.created_by
from P21Play.dbo.inv_mast
right outer join P21Play.dbo.fk_temp_new_items
on P21Play.dbo.inv_mast.item_desc+' '+isnull(P21Play.dbo.inv_mast.extended_desc,'')
= P21Play.dbo.fk_temp_new_items.item_desc+' '+isnull(P21Play.dbo.fk_temp_new_items.extd_desc,'')
group by
P21Play.dbo.fk_temp_new_items.inv_mast_uid,P21Play.dbo.fk_temp_new_items.item_id,
P21Play.dbo.fk_temp_new_items.item_desc,P21Play.dbo.fk_temp_new_items.extd_desc,
P21Play.dbo.fk_temp_new_items.date_created,P21Play.dbo.fk_temp_new_items.created_by
having count(P21Play.dbo.inv_mast.inv_mast_uid)>1'

 

[gmmastros]

You need to double the single-quotes, like this:

Set @query = 
'select 
P21Play.dbo.fk_temp_new_items.inv_mast_uid,P21Play.dbo.fk_temp_new_items.item_id,
P21Play.dbo.fk_temp_new_items.item_desc+'' ''+isnull(P21Play.dbo.fk_temp_new_items.extd_desc,''''),
P21Play.dbo.fk_temp_new_items.date_created,P21Play.dbo.fk_temp_new_items.created_by
from P21Play.dbo.inv_mast 
right outer join P21Play.dbo.fk_temp_new_items
on P21Play.dbo.inv_mast.item_desc+'' ''+isnull(P21Play.dbo.inv_mast.extended_desc,'''')
= P21Play.dbo.fk_temp_new_items.item_desc+'' ''+isnull(P21Play.dbo.fk_temp_new_items.extd_desc,'''')
group by
P21Play.dbo.fk_temp_new_items.inv_mast_uid,P21Play.dbo.fk_temp_new_items.item_id,
P21Play.dbo.fk_temp_new_items.item_desc,P21Play.dbo.fk_temp_new_items.extd_desc,
P21Play.dbo.fk_temp_new_items.date_created,P21Play.dbo.fk_temp_new_items.created_by
having count(P21Play.dbo.inv_mast.inv_mast_uid)>1'

Looking through your code, I would encourage you to use table aliases because it makes the code much easier to read and understand. I took the liberty of doing this for you so that you will (hopefully) appreciate the difference.

Set @query = '
select New.inv_mast_uid,
       New.item_id,
       New.item_desc+'' ''+isnull(New.extd_desc,''''),
       New.date_created,
       New.created_by
from   P21Play.dbo.inv_mast As Mast
       right outer join P21Play.dbo.fk_temp_new_items As New
         on Mast.item_desc+'' ''+isnull(Mast.extended_desc,'''')
            = New.item_desc+'' ''+isnull(New.extd_desc,'''')
group by
       New.inv_mast_uid,
       New.item_id,
       New.item_desc,
       New.extd_desc,
       New.date_created,
       New.created_by
having count(Mast.inv_mast_uid)>1
'

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom