CREATE   PROCEDURE [sp_process_new_entries]  AS

declare @c_SID nvarchar(10)
declare @name nvarchar(200)
declare @tmp_name nvarchar(200)

declare @fname nvarchar(32)
declare @mname nvarchar(32)
declare @lname nvarchar(32)
declare @filled int
declare @count int

declare @perm_street1 nvarchar(32)
declare @perm_street2 nvarchar(32)
declare @perm_city nvarchar(32)
declare @perm_state nvarchar(32)
declare @perm_zip nvarchar(32)
declare @perm_phone nvarchar(32)
declare @perm_email nvarchar(32)

declare @filld int

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_NAME = 'Improved_docket_tmpins')
   DROP TABLE Improved_docket_tmpins

-- Inserting docket_tmpins records in Improved_docket_tmpins table that generates rowIds for records

SELECT     Student_ID, First_Name, rowId = IDENTITY (int, 1, 1)
INTO            Improved_docket_tmpins
FROM        (Select Student_Id, First_Name from  dockets_tmpins) temp

declare @rowCount int
declare @maxRecordCount int

set @rowCount = 1

-- getting max students count
select @maxRecordCount = count(DISTINCT Student_Id) from Improved_docket_tmpins

while (@rowCount <= @maxRecordCount )
BEGIN
	set @filld = (select filled from buckets where bucket_time = '3/2/2007 12:30:00 PM')

	select @c_SID=student_id from Improved_docket_tmpins where rowId = @rowCount

	insert into dockets
	select * from dockets_tmpins
	where student_id = @c_SID

	-- Name split logic
	
	select @name=First_Name from Improved_docket_tmpins where student_id = @c_SID

	select @lname=item from fnSplit(@name,',') where colId=1
	select @tmp_name=item from fnSplit(@name,',') where colId=2

	select @fname=item from fnSplit(@tmp_name,' ') where colId=1
	select @mname=item from fnSplit(@tmp_name,' ') where colId=2

	-- update the entries		
	update dockets 
		set 	status='DOCKETED', 
			status_date=getdate(),
			interviewer_rating = 0,
			committee_rating =0,
			first_name = @fname,
			middle_name = @mname,
			last_name = @lname,
			local_street1 = perm_street1,
			local_street2 = perm_street2,
			local_city = perm_city,
			local_state  = perm_state,
			local_zip = perm_zip,
			local_phone = perm_phone,
			local_email = perm_email
	where student_ID = @c_SID

	-- Call the stored proc to assign the meeting date
	exec Assign_meeting_date @c_SID

	set @name = ''
	set @fname = ''
	set @mname = ''
	set @tmp_name = ''
	set @lname = ''

	set @rowCount = @rowCount + 1
	
END


delete from Improved_docket_tmpins
drop table Improved_docket_tmpins

--delete from dockets_tmpins
GO