moodle.waes.ac.uk/moodle.waes.ac.uk.zip/moodle.waes.ac.uk/TMG/SP1-TMG-KB981324-AMD64-ENU.msp/PCW_CAB_SHFx2/F2143_msfpcui.dll/BINARY/25173

Text File | 2010-06-15 | 2KB | 69 lines

CREATE PROCEDURE [dbo].[ISA_spTrimSummariesOnRsManager]
    @FromDate datetime,
    @ToDate datetime
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON

    PRINT   ' 1st day:'
    PRINT @FromDate
    PRINT   'last day:'
    PRINT @ToDate

    BEGIN TRANSACTION
    BEGIN TRY
        DECLARE @TrimSp varchar(300)

        IF EXISTS (SELECT [TrimSp] FROM [tblTrimmingRegistration])
        BEGIN
            DECLARE @StrSQL varchar(1000)

            DECLARE TrimSp_cursor CURSOR FOR
            SELECT [TrimSp] FROM [tblTrimmingRegistration]

            OPEN TrimSp_cursor
            FETCH NEXT FROM TrimSp_cursor INTO @TrimSp
            WHILE (@@FETCH_STATUS = 0)
            BEGIN
                SET @StrSQL = @TrimSp +
                ' @FromDate = ' + QUOTENAME(@FromDate, NCHAR(39)) +
                ',@ToDate = ' + QUOTENAME(@ToDate, NCHAR(39))

                PRINT 'Executing : ' + @StrSQL
                EXEC (@StrSQL)

                FETCH NEXT FROM TrimSp_cursor INTO @TrimSp
            END
            CLOSE TrimSp_cursor
            DEALLOCATE TrimSp_cursor
        END

        COMMIT TRANSACTION
        PRINT 'Successfully Trimmed array daily summaries'
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
        PRINT 'Unable to Trim array daily summaries'
        PRINT ERROR_MESSAGE()
        DECLARE
            @ErrorMessage    NVARCHAR(4000),
            @ErrorNumber     INT,
            @ErrorSeverity   INT,
            @ErrorState      INT,
            @ErrorLine       INT,
            @ErrorProcedure  NVARCHAR(200);

        SELECT
            @ErrorMessage = ERROR_MESSAGE(),
            @ErrorNumber = ERROR_NUMBER(),
            @ErrorSeverity = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE(),
            @ErrorLine = ERROR_LINE(),
            @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

        RAISERROR (@ErrorMessage, @ErrorSeverity, 1, @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine);
    END CATCH

END