checking for failed conditions in TSQL

Hi all,

while trying to hone my skils on T-SQL, i came across a procedure that

Load blank per-activity data files for multi-instance activities to a DB's BlankData table, then it should be

-- creating this table if BlankData isn't in the DB and recreating it otherwise.

THE PROCEDURE IS AS BELOW)

-- Directory containing files to load specified as a @path argument to this procedure.

-- Directory containing files to load specified as a @path argument to this procedure.

MY QUESTIONS:

1. should procedure warn if table already present before deleting BlankData?

2. HOW DO I :

-- -. add checks for the following conditions, with suitable messages
-- -. failed "exec xp_cmdshell @cmd" command
-- -. @path's referencing a directory that's devoid of .xml files
-- -. failed attempts to read .xml files
-- -. failed "select name from #filenames where name like '%.xml'" command
-- -. failed "exec (@sql)" command

3. -. should option be added for writing messages to a log?

4. HOW TO:

collapse all sp_Load<documentXX>toDB procedures to a single,
-- parameterized procedure

5.HOW DO I SPECIFY A SECOND PARAMAMETER TO
-- -. specifies qualifier (e.g., 201308) for table from which to load documents
-- -. defaults to value given by a new "current epoch" function
-- concern here is supporting multi-schema operation by allowing for extraction of different schema versions' of
-- blank documents from different tables in a set of related tables:
-- e.g.., BlankData_201308, BlankData_201309...

THE PROCEDURE

CREATE PROCEDURE [dbo].[sp_LoadBlankDataToDB]
@path varchar(256)
AS
BEGIN
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1

-- To update the currently configured value for advanced options.
RECONFIGURE

-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1

-- To update the currently configured value for this feature.
RECONFIGURE

SET NOCOUNT ON;
IF (EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'BlankData'))
BEGIN
print('exist')
drop table fas.dbo.[BlankData]
END

create table BlankData(id int identity(1,1),fileName varchar(256),blankFile xml)

declare @cmd varchar(256)
set @cmd = 'dir /b ' +'"'+ @path+'"'
create table #filenames(name varchar(256))

insert into #filenames
exec xp_cmdshell @cmd
declare @file nvarchar(256)

declare fileNameCursor CURSOR SCROLL FOR
select name from #filenames where name like '%.xml'

open fileNameCursor
fetch next from fileNameCursor
into @file

WHILE @@FETCH_STATUS = 0

begin
declare @sql varchar(max)

--insert into fas.dbo.SampleData(fileName) values (@file)

set @sql =
'insert into [fas].[dbo].[BlankData]
select '''+@file+''', * from openrowset (BULK N'''+@path+'\'+@file+''', SINGLE_BLOB) as xmlfile'

exec (@sql)
FETCH NEXT FROM fileNameCursor
INTO @file

end

CLOSE fileNameCursor
DEALLOCATE fileNameCursor

DECLARE @fileCount int
select @fileCount = COUNT(*) from #filenames
print ('There are '+ convert(varchar(max),(@fileCount-1)) + ' files under the directory')
select @fileCount = COUNT(*) from BlankData
print (convert(varchar(max),@fileCount) +' xml files are imported')

select name as 'File Not Imported'
from #filenames
where name not in
(select fileName from fas.dbo.BlankData)

select fileName as 'File Imported'
from BlankData

END
GO