Support » Поддержка Digispot II

--USE [pioner]

DECLARE @ver int

SET @ver = 4

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[components]') AND type in (N'U'))

BEGIN

        IF EXISTS( SELECT veri FROM [components] WHERE name='mdb.ph_history' AND veri>=@ver )

        BEGIN

                PRINT N'Current PH_HISTORY version is equal or higher than '+ CAST(@ver AS varchar(10))

                --RAISERROR('Script terminated...',16,1) 

                RETURN        

        END                

END

ELSE

BEGIN

        PRINT N'Components table not found. Script exit'

        RETURN

END

BEGIN TRY

BEGIN TRAN

PRINT N'Script begin'

--добавляем колонку

EXEC sp_executesql 

N'

ALTER TABLE [dbo].[PH_HISTORY] ADD [GroupID] [int] NULL

PRINT ''Column GroupID added to table [PH_HISTORY]''

'

-- вычисляем значение для этой колонки

EXEC sp_executesql 

N'

UPDATE ph_hist

SET ph_hist.GroupID = 

(        

        SELECT MIN(ph_hist_sub.ID)

        FROM PH_HISTORY as ph_hist_sub

        WHERE ph_hist_sub.PH_ID = ph_hist.PH_ID

                AND ph_hist_sub.UserName = ph_hist.UserName

                AND ph_hist_sub.HostName = ph_hist.HostName

                AND ABS(DATEDIFF(second, ph_hist_sub.ModifyDate, ph_hist.ModifyDate)) < 5 

)

FROM [dbo].[PH_HISTORY] AS ph_hist

WHERE ph_hist.GroupID IS NULL

PRINT ''GroupID calculated for existing items''

'

UPDATE [dbo].[components] 

SET veri = @ver 

WHERE name='mdb.ph_history'

COMMIT TRAN

PRINT N'Script end!'

END TRY

BEGIN CATCH

        ROLLBACK TRAN

        PRINT N'Script error'

END CATCH;