SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


ALTER            procedure run_counts as

declare @count_id varchar(6)
declare @where_clause nvarchar(2000)
declare @querystring nvarchar(4000)
declare @list_table varchar(25)
declare @list_id varchar(3)
declare @user_name varchar(35)
declare @count_name varchar(255)

--grab the top most count that is not yet processed
set @count_id = (select top 1 count_id from counts where completed = 'Y')

--set its status to processing if it has not yet been set
update counts
set completed = 'P'
where count_id = @count_id and completed = 'Y'

if (@@rowcount!=1)
	begin
	print ('ROWCOUNT WAS: ' + cast (@@rowcount as varchar(25)))
	print ('There were no counts to execute')
	return
	end

set @where_clause = ''

--grab the where clause out of the counts table
set @where_clause = (select sql_stmt from counts where count_id = @count_id)


--replace the @ symbol with ' for our sql
if (patindex('%@%',@where_clause) != 0)
	begin
		set @where_clause = (select replace(@where_clause, '@', ''''))
	end

-- check for preexisting count_ table, drop it if needed

EXEC('if exists (select name from sysobjects where name=''count_'+@count_id+''')
		 BEGIN drop table count_'+@count_id+' END')

--grab the list id
set @list_id = (select top 1 list_id from counts where count_id = @count_id)

--grab our user name
set @user_name = (select top 1 user_id from counts where count_id = @count_id)
print @user_name

--grab our table name
set @querystring = N'set @list_table = (select table_name from lists where list_id = ''' + @list_id + ''')'
exec sp_executesql @querystring, N'@list_table varchar(25) output', @list_table output


--create the query from our stored where clause
set @querystring = 'select zip,count(zip) as count into count_'+@count_id+' from matt.dbo.'+@list_table+' where '+@where_clause+' group by zip order by zip'
exec sp_executesql @querystring

--index the table
set @querystring = 'create index zip on count_' + @count_id + '(zip)'
exec sp_executesql @querystring

set @querystring = 'create index count on count_' + @count_id + '(count)'
exec sp_executesql @querystring



--grab our count_name
set @count_name = (select top 1 count_name from counts where count_id = @count_id)

--mark as "C" for completed when done executing the count
update counts
set completed = 'C'
where count_id = @count_id

--grant select to tools
set @querystring = 'grant select on count_' + @count_id + ' to tools'
exec sp_executesql @querystring

--email the results

declare @execstring nvarchar(1000)
	declare @subj nvarchar(50)
	declare @msg nvarchar(4000)
	declare @rc int
	

	if((@user_name != rtrim('scott')) and (@user_name != rtrim('matt')))
	begin
		print 'invalid user name!' + @user_name
		return
	end

	if(@user_name = 'scott')
	begin	
		set @user_name = 'scott@somewhere.com'
	end
	
	if(@user_name = 'matt')
	begin
		set @user_name = 'matt@somewhere.com'
	end

	
	set @subj = N'Count ' + @count_name +'(' + @count_id + ') has finished!'
	set @msg = N'The count you had performed on the Matt_counts system has been completed.' +
	'<br>count id:   ' + @count_id +
	'<br>count name: ' + @count_name +
	'<br>table name: ' + @list_table

	exec @rc = master.dbo.xp_smtp_sendmail
	@FROM       = N'matt_counts_system@somewhere.com',
	@TO         = @user_name,
    	@subject    = @subj,
        @message    = @msg,
        @type       = N'text/html',
	@server     = N'somewhere.com'
	select RC = @rc 












GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO