SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


ALTER     procedure Add_Order (@order_name varchar(20), @table_name varchar(20)) As

declare @qty nvarchar(15)
declare @execstring nvarchar(4000)
declare @current_date datetime
declare @transaction_complete binary

set @transaction_complete = 0

begin transaction

print 'check to see if our table exists'
exec('if exists (select name from sysobjects where name='''+@order_name+''')
		 BEGIN 
			drop table '+@order_name+' 
			delete orders where order_name = ''' + @order_name + '''' + 
'  END')
if @@error <> 0 goto PROBLEM

print 'copy data into matt_counts'
--select data into matt_counts
exec('select * into ' + @order_name + ' from matt.dbo.' +
@order_name)
if @@error <> 0 goto PROBLEM

print'create our index on matchcode'
--create our index on matchcode
exec('create index matchcode on matt_counts.dbo.' + @order_name 
+ '(matchcode)')
if @@error <> 0 goto PROBLEM

print 'grab our quantity to be added'
set @execstring = N'set @qty = (select count(*) from ' + @order_name + ')'
exec sp_executesql @execstring, N'@qty nvarchar(15) output', @qty output
if @@error <> 0 goto PROBLEM

print('grab the current date')
--grab the current date
set @execstring = N'set @current_date = getdate()'
exec sp_executesql @execstring, N'@current_date datetime output', @current_date output
if @@error <> 0 goto PROBLEM

print('insert our record into matt_counts.orders table')
--modify our orders table
exec('insert into matt_counts.dbo.orders values(''' + @table_name 
+ ''', ''00000'', ''' + @order_name + ''', ''' + @qty + ''', ''C'', ''' + @current_date + ''')')
if @@error <> 0 goto PROBLEM

print 'grant select to tools'
exec('grant all on '+ @order_name + ' to tools')
if @@error <> 0 goto PROBLEM

set @transaction_complete = 1
commit transaction



-- if our transaction isnt complete, rollback
PROBLEM:
begin
	if @transaction_complete <> 1
	begin
		print('-----------> There was an error in ADD_ORDER, no changes were made!')	
		print 'transaction count: ' + cast (@@trancount as varchar(10))
		rollback transaction
	end
end



GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO