SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Test Condition after each line of code
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bconner
Starting Member

USA
48 Posts

Posted - 02/26/2014 :  14:48:33  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000