CREATE PROCEDURE dbo.zz_populate_email_endings AS

Begin 

Declare @company varchar(100), 
	@coID int, 
	@results int, 
	@emailending varchar(100) 

Declare Cursid Cursor for
	Select company_id from sales_company order by company_id

Create Table #temp1 (Ending Varchar(200), Company varchar(200), EmailCount int) 

open Cursid
fetch next from cursid into @coID
while (@@fetch_status<>-1) 
Begin 

select @company=company_name from sales_company where company_id=@coID

Insert into #temp1
	select 
		substring(email, charindex('@', email), len(email)+1-charindex('@', email)) as [EmailEnding], 
		max(isnull(company_name, 'n/a')),
		count(substring(email, charindex('@', email), len(email)+1-charindex('@', email))) as EmailTotal
	from 
		smc_new_products.dbo.usr_smc 
	where 
		company_name like @company+'%'
	or 
		email like '@' + @company + '%'
	group by substring(email, charindex('@', email), len(email)+1-charindex('@', email))
	order by emailtotal desc

--Find out if there are 0 results, 1 result, or many results here
--?

if @results=0 
	begin 
		update sales_company 
		set multiple_hits=0
		where company_id=@coID
	end 

if @results=1 
	begin 
		update sales_company 
		set multiple_hits=0
		where company_id=@coID

		exec ap_link_email_company @company, @emailending
	end 

if @results>1 
	begin 
		update sales_company 
		set multiple_hits=1
		where company_id=@coID
	end 

--reset stuff
truncate table #temp1

fetch next from cursid into @coID
End 

deallocate Cursid

Print 'complete'

End
GO

quote:

---

Originally posted by X002548

DECLARE @Results int, @Error int
SELECT @Results = @@ROWCOUNT, @Error = @@ERROR

---