You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
76 lines
1.6 KiB
Transact-SQL
76 lines
1.6 KiB
Transact-SQL
-- ================================================
|
|
-- Template generated from Template Explorer using:
|
|
-- Create Procedure (New Menu).SQL
|
|
--
|
|
-- Use the Specify Values for Template Parameters
|
|
-- command (Ctrl-Shift-M) to fill in the parameter
|
|
-- values below.
|
|
--
|
|
-- This block of comments will not be included in
|
|
-- the definition of the procedure.
|
|
-- ================================================
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
-- =============================================
|
|
-- Author: SafetyRally
|
|
-- Create date: 16/08/2010
|
|
-- Description: P1
|
|
-- =============================================
|
|
drop procedure ProcP1
|
|
go
|
|
CREATE PROCEDURE ProcP1
|
|
|
|
AS
|
|
BEGIN
|
|
-- SET NOCOUNT ON added to prevent extra result sets from
|
|
-- interfering with SELECT statements.
|
|
SET NOCOUNT ON;
|
|
begin transaction
|
|
|
|
DECLARE @unitID bigint
|
|
DECLARE @datetime datetime
|
|
|
|
DECLARE db_cursor CURSOR FOR
|
|
select unitid, max(data)
|
|
from Rota
|
|
group by unitid
|
|
|
|
|
|
OPEN db_cursor
|
|
FETCH NEXT FROM db_cursor INTO @unitID, @datetime
|
|
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
|
|
insert into rotaHistorico
|
|
SELECT [UnitID]
|
|
,[data]
|
|
,[latitude]
|
|
,[longitude]
|
|
,[velocidade]
|
|
,[rumo]
|
|
,[altitude]
|
|
,[satelites]
|
|
,[inputs]
|
|
,[outputs]
|
|
,[customInput_1]
|
|
,[customInput_2]
|
|
,[razaoSMSID]
|
|
FROM [srgprs].[dbo].[Rota]
|
|
where UnitID = @unitID and data < @datetime
|
|
|
|
delete from Rota where UnitID = @unitID and data < @datetime
|
|
|
|
FETCH NEXT FROM db_cursor INTO @unitID, @datetime
|
|
END
|
|
|
|
CLOSE db_cursor
|
|
DEALLOCATE db_cursor
|
|
|
|
commit transaction
|
|
|
|
END
|
|
GO
|