[T-SQL] Help needed-checking TSQL statements for errors

---

Help needed-checking TSQL statements for errors Posted: 13 Jul 2013 03:16 AM PDT while trying to hone my skils on T-SQL, i came across a procedure thatLoad 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)" command3. -. should option be added for writing messages to a log?4. HOW TO: collapse all sp_Load<documentXX>toDB procedures to a single,-- parameterized procedure5.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 PROCEDURECREATE PROCEDURE [dbo].[sp_LoadBlankDataToDB] @path varchar(256)ASBEGIN -- 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 ENDGO

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610