Test Condition after each line of code

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Test Condition after each line of code

Author

Topic

bconner
Starting Member

48 Posts

Posted - 2014-02-26 : 14:48:33

All,
I have a stored procedure that creates Indexes and I want to write the code to test the available percent free disc space after each index is created. I have a stored procedure named AdHoc.dbo.Update_DiskStats_Table that updates table AdHoc.dbo.Disk_Available with the precent of free space

Below is an example (I am new to TSQL)


DECLARE @intPercent AS FLOAT
  
  EXEC AdHoc.dbo.Update_DiskStats_Table
  
 SET @intPercent = SELECT col2 FROM AdHoc.dbo.Disk_Available
  IF @intPercent >=4 THEN
   BEGIN TRANSACTION
     CREATE NONCLUSTERED INDEX IX_AccessionNumber ON Transaction_Archive (Accession_Number ASC)
   END TRANSACTION 
  ELSE
   ROLLBACK TRANSACTION

I want to update the table AdHoc.dbo.Disk_Available with the disk percent available after each index is created and test to make sure the disk space available is at or above 4% before the next index updates.

The problem is I have about 13 indexes that need to be updated how can I write the code to test to make sure the percent free space is at or above 4% before the next Index is created? Without having to write the code above for each Create Index. Is there an easier way to write the code?

Below are the indexes


CREATE NONCLUSTERED INDEX IX_CurrentFSC                    ON Transaction_Archive (Current_FSC ASC)

  CREATE NONCLUSTERED INDEX IX_Current_FSC_RptCat2           ON Transaction_Archive (Current_FSC_RptCat2 ASC)

  CREATE NONCLUSTERED INDEX IX_DateOfService                 ON Transaction_Archive (Service_Date ASC)

  CREATE NONCLUSTERED INDEX IX_Division_Name                 ON Transaction_Archive (Division_Name ASC)

  CREATE NONCLUSTERED INDEX IX_Inv_DOS						 ON Transaction_Archive (Inv_DOS ASC)

  CREATE NONCLUSTERED INDEX IX_Location_Name_Mnemonic        ON Transaction_Archive (Location_Name ASC, Location_Mnemonic ASC)

  CREATE NONCLUSTERED INDEX IX_Modifier_1		             ON Transaction_Archive (Modifier_1 ASC)

  CREATE NONCLUSTERED INDEX IX_Original_FSC                  ON Transaction_Archive (Original_FSC ASC)

  CREATE NONCLUSTERED INDEX IX_Original_FSC_RtpCat2          ON Transaction_Archive (Original_FSC_RptCat2 ASC)

  CREATE NONCLUSTERED INDEX IX_Paycode                       ON Transaction_Archive (PAY_CODE_NUMBER ASC)

  CREATE NONCLUSTERED INDEX IX_Procedure_Code				 ON Transaction_Archive (Procedure_Code ASC)

  CREATE NONCLUSTERED INDEX IX_ReferringLocation             ON Transaction_Archive (Ref_Name ASC)

  CREATE NONCLUSTERED INDEX IX_Rej_1			             ON Transaction_Archive (Rej_1 ASC)

Any help is appreciated.....

Brian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources