ÿþSET QUOTED_IDENTIFIER ON 

GO

SET ANSI_NULLS ON 

GO





CREATE                procedure run_extracts as



declare @count_id varchar(6)

declare @where_clause nvarchar(2000)

declare @querystring nvarchar(4000)

declare @table_name varchar(25)

declare @quantity varchar(12)

declare @user_name varchar(35)

declare @count_name varchar(255)

declare @orderName varchar(255)



--grab the top most count that is not yet processed

set @orderName = (select top 1 order_name from orders where status = 'Y')





--set its status to processing if it has not yet been set

update orders

set status = 'P'

where order_name = @orderName and status = 'Y'

if (@@rowcount!=1)

	begin

	print ('ROWCOUNT WAS: ' + cast (@@rowcount as varchar(25)))

	print ('There were no counts to execute')

	return

	end





print 'Order Name: ' + @orderName

--grab count_id

set @count_id = (select top 1 count_id from orders where order_name = @orderName)

print 'Count ID: ' + @count_id



set @quantity = (select top 1 quantity from orders where order_name = @orderName)

print 'Quantity ordered: ' + @quantity



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 order table, drop it if needed



print 'Where clause: ' + @where_clause

print 'dropping preexisting table'



exec('if exists (select name from sysobjects where name=''['+@orderName+''')

		 BEGIN drop table matt_counts.dbo.['+@orderName+'] END')



print 'grabbing user name' 

--grab our user name

set @user_name = (select top 1 user_id from counts where count_id = @count_id)

print 'User Name: ' + @user_name



--grab our table name

set @table_name = (select top 1 table_name from orders where order_name = @orderName)

print 'Table name: ' + @table_name





--execute our query!!!!!

set @querystring = 'select top ' + @quantity + ' * into matt_counts.dbo.['+@orderName+'] from matt.dbo.'+@table_name+' where '+@where_clause+' order by nth_guide desc'

exec sp_executesql @querystring



--index the table

set @querystring = 'create index zip on [' + @orderName + '](zip)'

exec sp_executesql @querystring



set @querystring = 'create index matchcode on [' + @orderName + '](matchcode)'

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 orders

set status = 'C'

where order_name = @orderName



--grant select to tools

set @querystring = 'grant select on [' + @orderName + '] 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'Order ' + @orderName +'(c' + @count_id + ') has finished!'

	set @msg = N'The ORDER you had performed on the Matt_counts system has been completed.' +

	'<br>count id:   ' + @count_id +

	'<br>order name: m' + @orderName +

	'<br>count name: ' + @count_name +

	'<br>table name: ' + @table_name



	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