If I understand correctly, you want to use the results of the stored procedure to update the contacts. Can the update of the contact be done within the stored procedcure? If so, use the query that you're already got as a corralated sub-query.


CREATE PROCEDURE sp_ageContacts AS
declare @d1Year datetime, @d2Years datetime

select @d1Year = getdate() - 365
select @d2Years = getdate() - 365 * 2

UPDATE contacts
SET iRely = (
select 'Reliablity' =
case
when dUpdated >= @d1Year or max(actions.dDate) >= @d1Year then 1
when dUpdated >= @d2Years and max(actions.dDate) >= @d2Years then 2
when dUpdated >= @d2Years and (max(actions.dDate)) IS NULL then 3
when dUpdated < @d2Years and (max(actions.dDate)) >= @d2Years then 3
when dUpdated < @d2Years and max(actions.dDate) < @d2Years then 4
when dUpdated < @d2Years and (max(actions.dDate)) IS NULL then 5
when dUpdated is NULL and (max(actions.dDate)) IS NULL then 5
else NULL
end
from contacts c left join relts on c. cContactID = relts.cSecID
left join actions on relts.cPriID = actions.cActionID
WHERE c.contactid = contacts.ccontactid
group by c. contacts.cContactID)

/* end of procedure */

-Mike




>Hi All,
>
>I'm trying to assign a "reliability of information" value to my contacts table. The stored procedure below selects the values I want. How do I apply these values to the contacts table using an "Update". The field, cContactID, is the primary key. iRely is the field I want to update.
>
>TIA!
>
>Bill
>
>CREATE PROCEDURE sp_ageContacts AS
>declare @d1Year datetime, @d2Years datetime
>
>select @d1Year = getdate() - 365
>select @d2Years = getdate() - 365 * 2
>
>select 'Reliablity' =
>case
> when dUpdated >= @d1Year or max(actions.dDate) >= @d1Year then 1
> when dUpdated >= @d2Years and max(actions.dDate) >= @d2Years then 2
> when dUpdated >= @d2Years and (max(actions.dDate)) IS NULL then 3
> when dUpdated < @d2Years and (max(actions.dDate)) >= @d2Years then 3
> when dUpdated < @d2Years and max(actions.dDate) < @d2Years then 4
> when dUpdated < @d2Years and (max(actions.dDate)) IS NULL then 5
> when dUpdated is NULL and (max(actions.dDate)) IS NULL then 5
> else NULL
>end
>from contacts left join relts on cContactID = relts.cSecID
> left join actions on relts.cPriID = actions.cActionID
>group by contacts.cContactID
>
>/* end of procedure */