Error handling in some of TSQL commands. An example is how to handle errors in the EX

[yommy1831]

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

[Rabbit]

Please use code tags when posting code.

We only allow one question per thread on the forum. So I will answer the first one.

As far as whether or not you should warn the user, that's up to you. It depends on your requirements. For some people, they need to keep the old data, for others, they don't because they're only using it as a staging table or it's just temporary data. Whether or not you want to warn the user depends on which camp you fall into.

[yommy1831]

Thanks for your response.