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
   

- Advertisement -