IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'RethrowError')
BEGIN
EXEC('CREATE PROCEDURE [dbo].RethrowError AS RETURN')
END
GO
ALTER PROCEDURE RethrowError AS
/* Return if there is no error information to retrieve. */
IF ERROR_NUMBER() IS NULL
RETURN;
DECLARE
@ErrorMessage NVARCHAR(4000),
@ErrorNumber INT,
@ErrorSeverity INT,
@ErrorState INT,
@ErrorLine INT,
@ErrorProcedure NVARCHAR(200);
/* Assign variables to error-handling functions that
capture information for RAISERROR. */
SELECT
@ErrorNumber = ERROR_NUMBER(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorLine = ERROR_LINE(),
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
/* Building the message string that will contain original
error information. */
SELECT @ErrorMessage =
N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' +
'Message: '+ ERROR_MESSAGE();
/* Raise an error: msg_str parameter of RAISERROR will contain
the original error information. */
RAISERROR(@ErrorMessage, @ErrorSeverity, 1,
@ErrorNumber, /* parameter: original error number. */
@ErrorSeverity, /* parameter: original error severity. */
@ErrorState, /* parameter: original error state. */
@ErrorProcedure, /* parameter: original error procedure name. */
@ErrorLine /* parameter: original error line number. */
);
GO
----------------------------------------------------------------
-- [dbo].[Device] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertDevice')
BEGIN
EXEC('CREATE PROCEDURE [dbo].[InsertDevice] AS RETURN')
END
GO
ALTER PROCEDURE [dbo].[InsertDevice]
@creationDate datetime,
@iD uniqueidentifier,
@lastLoginDate datetime
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY
INSERT INTO [dbo].[Device] ([CreationDate], [ID], [LastLoginDate])
VALUES (@creationDate, @iD, @lastLoginDate)
END TRY
BEGIN CATCH
EXEC RethrowError;
END CATCH
SET NOCOUNT OFF
END
GO
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdateDevice')
BEGIN
EXEC('CREATE PROCEDURE [dbo].[UpdateDevice] AS RETURN')
END
GO
ALTER PROCEDURE [dbo].[UpdateDevice]
@creationDate datetime,
@iD uniqueidentifier,
@lastLoginDate datetime
AS
BEGIN
--The [dbo].[Device] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
SET NOCOUNT ON
BEGIN TRY
UPDATE [dbo].[Device]
SET [CreationDate] = @creationDate, [LastLoginDate] = @lastLoginDate
WHERE [ID]=@iD
IF @@ROWCOUNT = 0
BEGIN
RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
END
END TRY
BEGIN CATCH
EXEC RethrowError;
END CATCH
SET NOCOUNT OFF
END
GO
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeleteDevice')
BEGIN
EXEC('CREATE PROCEDURE [dbo].[DeleteDevice] AS RETURN')
END
GO
ALTER PROCEDURE [dbo].[DeleteDevice]
@iD uniqueidentifier
AS
BEGIN
SET NOCOUNT ON
DELETE FROM [dbo].[Device]
WHERE [ID]=@iD
SET NOCOUNT OFF
END
GO
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromDevice')
BEGIN
EXEC('CREATE PROCEDURE [dbo].[GetAllFromDevice] AS RETURN')
END
GO
ALTER PROCEDURE [dbo].[GetAllFromDevice]
AS
BEGIN
SET NOCOUNT ON
SELECT
[device].[CreationDate] AS 'CreationDate',
[device].[ID] AS 'ID',
[device].[LastLoginDate] AS 'LastLoginDate'
FROM [dbo].[Device] [device]
SET NOCOUNT OFF
END
GO
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetDeviceByID')
BEGIN
EXEC('CREATE PROCEDURE [dbo].[GetDeviceByID] AS RETURN')
END
GO
ALTER PROCEDURE [dbo].[GetDeviceByID]
@iD uniqueidentifier
AS
BEGIN
SET NOCOUNT ON
SELECT
[device].[CreationDate] AS 'CreationDate',
[device].[ID] AS 'ID',
[device].[LastLoginDate] AS 'LastLoginDate'
FROM [dbo].[Device] [device]
WHERE [ID]=@iD
SET NOCOUNT OFF
END
GO
----------------------------------------------------------------
-- [dbo].[LocationEntry] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertLocationEntry')
BEGIN
EXEC('CREATE PROCEDURE [dbo].[InsertLocationEntry] AS RETURN')
END
GO
ALTER PROCEDURE [dbo].[InsertLocationEntry]
@heading float = NULL,
@iD uniqueidentifier,
@iDDevice uniqueidentifier,
@latitude float = NULL,
@longitude float = NULL,
@quote float = NULL,
@speed float = NULL,
@whenInserted datetime = NULL
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY
INSERT INTO [dbo].[LocationEntry] ([Heading], [ID], [IDDevice], [Latitude], [Longitude], [Quote], [Speed], [WhenInserted])
VALUES (@heading, @iD, @iDDevice, @latitude, @longitude, @quote, @speed, @whenInserted)
END TRY
BEGIN CATCH
EXEC RethrowError;
END CATCH
SET NOCOUNT OFF
END
GO
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdateLocationEntry')
BEGIN
EXEC('CREATE PROCEDURE [dbo].[UpdateLocationEntry] AS RETURN')
END
GO
ALTER PROCEDURE [dbo].[UpdateLocationEntry]
@heading float = NULL,
@iD uniqueidentifier,
@iDDevice uniqueidentifier,
@latitude float = NULL,
@longitude float = NULL,
@quote float = NULL,
@speed float = NULL,
@whenInserted datetime = NULL
AS
BEGIN
--The [dbo].[LocationEntry] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
SET NOCOUNT ON
BEGIN TRY
UPDATE [dbo].[LocationEntry]
SET [Heading] = @heading, [IDDevice] = @iDDevice, [Latitude] = @latitude, [Longitude] = @longitude, [Quote] = @quote, [Speed] = @speed, [WhenInserted] = @whenInserted
WHERE [ID]=@iD
IF @@ROWCOUNT = 0
BEGIN
RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
END
END TRY
BEGIN CATCH
EXEC RethrowError;
END CATCH
SET NOCOUNT OFF
END
GO
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeleteLocationEntry')
BEGIN
EXEC('CREATE PROCEDURE [dbo].[DeleteLocationEntry] AS RETURN')
END
GO
ALTER PROCEDURE [dbo].[DeleteLocationEntry]
@iD uniqueidentifier
AS
BEGIN
SET NOCOUNT ON
DELETE FROM [dbo].[LocationEntry]
WHERE [ID]=@iD
SET NOCOUNT OFF
END
GO
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromLocationEntry')
BEGIN
EXEC('CREATE PROCEDURE [dbo].[GetAllFromLocationEntry] AS RETURN')
END
GO
ALTER PROCEDURE [dbo].[GetAllFromLocationEntry]
AS
BEGIN
SET NOCOUNT ON
SELECT
[locationEntry].[Heading] AS 'Heading',
[locationEntry].[ID] AS 'ID',
[locationEntry].[IDDevice] AS 'IDDevice',
[locationEntry].[Latitude] AS 'Latitude',
[locationEntry].[Longitude] AS 'Longitude',
[locationEntry].[Quote] AS 'Quote',
[locationEntry].[Speed] AS 'Speed',
[locationEntry].[WhenInserted] AS 'WhenInserted'
FROM [dbo].[LocationEntry] [locationEntry]
SET NOCOUNT OFF
END
GO
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetLocationEntryByID')
BEGIN
EXEC('CREATE PROCEDURE [dbo].[GetLocationEntryByID] AS RETURN')
END
GO
ALTER PROCEDURE [dbo].[GetLocationEntryByID]
@iD uniqueidentifier
AS
BEGIN
SET NOCOUNT ON
SELECT
[locationEntry].[Heading] AS 'Heading',
[locationEntry].[ID] AS 'ID',
[locationEntry].[IDDevice] AS 'IDDevice',
[locationEntry].[Latitude] AS 'Latitude',
[locationEntry].[Longitude] AS 'Longitude',
[locationEntry].[Quote] AS 'Quote',
[locationEntry].[Speed] AS 'Speed',
[locationEntry].[WhenInserted] AS 'WhenInserted'
FROM [dbo].[LocationEntry] [locationEntry]
WHERE [ID]=@iD
SET NOCOUNT OFF
END
GO
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetLocationEntryByIDDevice')
BEGIN
EXEC('CREATE PROCEDURE [dbo].[GetLocationEntryByIDDevice] AS RETURN')
END
GO
ALTER PROCEDURE [dbo].[GetLocationEntryByIDDevice]
@iDDevice uniqueidentifier
AS
BEGIN
SET NOCOUNT ON
SELECT
[locationEntry].[Heading] AS 'Heading',
[locationEntry].[ID] AS 'ID',
[locationEntry].[IDDevice] AS 'IDDevice',
[locationEntry].[Latitude] AS 'Latitude',
[locationEntry].[Longitude] AS 'Longitude',
[locationEntry].[Quote] AS 'Quote',
[locationEntry].[Speed] AS 'Speed',
[locationEntry].[WhenInserted] AS 'WhenInserted'
FROM [dbo].[LocationEntry] [locationEntry]
WHERE [IDDevice]=@iDDevice
SET NOCOUNT OFF
END
GO
Submit an article or tip