3zMRpVhB

1begin try
2	begin tran;
3
4	exec sp_rename 'AlertLog', 'AlertLogOld';
5
6	CREATE TABLE [dbo].[AlertLog](
7		[AlertLogId] [int] IDENTITY(1,1) NOT NULL,
8		[Timestamp] [datetime] NULL,
9		[RoomId] [nvarchar](50) NULL,
10		[PatientId] [nvarchar](50) NULL,
11		[VentId] [nvarchar](50) NULL,
12		[MailTo] [nvarchar](50) NULL,
13		[MailAddr] [nvarchar](255) NULL,
14		[MailSubject] [nvarchar](100) NULL,
15		[MailText] [nvarchar](255) NULL,
16		[Status] [varchar](255) NULL,
17		CONSTRAINT [PK_AlertLog] PRIMARY KEY CLUSTERED 
18	(
19		[AlertLogId] ASC
20	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
21	) ON [PRIMARY];
22
23	insert	AlertLog
24	SELECT	[Timestamp]
25			,[RoomId]
26			,[PatientId]
27			,[VentId]
28			,[MailTo]
29			,[MailAddr]
30			,[MailSubject]
31			,[MailText]
32			,[Status]
33	FROM	[dbo].[AlertLogOld]
34	order by AlertLogId;
35
36	IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AppendToAlertLog]') AND type in (N'P', N'PC'))
37		exec ('DROP PROCEDURE [dbo].[AppendToAlertLog]');
38
39	exec ('CREATE PROCEDURE [dbo].[AppendToAlertLog] 
40			-- Add the parameters for the stored procedure here
41			@RoomId nvarchar(50),
42			@PatientId nvarchar(50),
43			@VentId nvarchar(50),
44			@MailTo nvarchar(50),
45			@MailAddr varchar(255),
46			@MailSubject varchar(100),
47			@MailText varchar(255),
48			@RecordId int OUTPUT,
49			@Status varchar(255) = NULL
50	AS
51	BEGIN
52		insert	AlertLog
53				([timestamp], RoomId, PatientId, VentId, MailTo, MailAddr, MailSubject, MailText, Status)
54		values	(GETUTCDATE(), @RoomId, @PatientId, @VentId, @MailTo, @MailAddr, @MailSubject, @MailText, @Status);
55
56		set @RecordId = @@IDENTITY
57	END');
58
59	commit tran;
60end try
61begin catch
62	rollback tran;
63	select	ERROR_MESSAGE()
64end catch