Help needed-checking TSQL statements for errors

• yomidara

  Newbie

  Points: 7

  More actions

  July 13, 2013 at 10:16 am

  #295536

  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