ALTER PROCEDURE [dbo].[usp_GetErrorInfo]  
AS  

DECLARE @ErrorNumber INT
SET @ErrorNumber=ERROR_NUMBER()

DECLARE @ErrorSeverity INT
SET @ErrorSeverity=ERROR_SEVERITY()

DECLARE @ErrorState INT
SET @ErrorState=ERROR_STATE()

DECLARE @ErrorLine INT
SET @ErrorLine=ERROR_LINE()

DECLARE @ErrorMessage NVARCHAR(max)
SET @ErrorMessage=ERROR_MESSAGE()

--SELECT  
    
--    ERROR_NUMBER() AS ErrorNumber  
--    ,ERROR_SEVERITY() AS ErrorSeverity  
--    ,ERROR_STATE() AS ErrorState  
--    ,ERROR_PROCEDURE() AS ErrorProcedure  
--    ,ERROR_LINE() AS ErrorLine  
--    ,ERROR_MESSAGE() AS ErrorMessage;  


RAISERROR (@ErrorMessage, -- Message text.  
               @ErrorSeverity, -- Severity.  
               @ErrorState -- State.  
               ); 

 if index already exists then it will rebuild elase generate a new one.

IF NOT EXISTS (
SELECT *
FROM sys.indexes
WHERE name='IX_StyleSize' AND object_id = OBJECT_ID('dbo.StyleSize') )

BEGIN
        PRINT 'index not found stylesize'

        BEGIN TRY
           
       
               
        BEGIN TRANSACTION
       
        CREATE NONCLUSTERED INDEX IX_StyleSize ON dbo.StyleSize
            (
            GroupID,
            PrdID,
            BTID,
            CBTID,
            SSID,
            sBarcode,
            SupID
            ) WITH ( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
       
        ALTER TABLE dbo.StyleSize SET (LOCK_ESCALATION = TABLE)
    
        --COMMIT

        END TRY
        BEGIN catch
            IF @@TRANCOUNT > 0 
                ROLLBACK TRANSACTION; 
            EXECUTE usp_GetErrorInfo; 
        END CATCH
        
        IF @@TRANCOUNT > 0 
            COMMIT TRANSACTION;

END
ELSE
BEGIN

    BEGIN TRY
       
        PRINT 'index found on stylesize rebuilding...'

        BEGIN TRANSACTION
            ALTER INDEX ALL ON dbo.StyleSize REBUILD;
        COMMIT TRANSACTION


        PRINT 'stylesize index rebuild successfull....'   
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 
            ROLLBACK TRANSACTION; 
        --EXECUTE usp_GetErrorInfo; 
        EXEC usp_GetErrorInfo
    END CATCH
    
END