GeQsJbti

1TABLICA Podaci
2
3CREATE TABLE Podaci (
4    data_id INT PRIMARY KEY IDENTITY (1, 1),
5    first_name VARCHAR (50) NOT NULL,
6    last_name VARCHAR (50) NOT NULL,
7    city VARCHAR (50) NOT NULL,
8    zip_code VARCHAR (10) NOT NULL,
9    phone_number VARCHAR(20) 
10);
11
12
13PROCEDURA InsertDataWithLogging
14-ukoliko zip code nije korektno formatiran (5 brojki) i/ili ukoliko osoba s tim podatcima vec postoji u bazi,
15u log se zapisuje poruka
16-ukoliko je zadovoljen uvjet, no postoji pogreska u TRY dijelu, takoder se u log zapisuje poruka greske
17-inace se zapisuju podatci u bazu
18
19CREATE PROCEDURE InsertDataWithLogging
20    @first_name VARCHAR (50),
21    @last_name VARCHAR (50),
22    @city VARCHAR (50),
23    @zip_code VARCHAR (10),
24    @phone_number VARCHAR(20)
25AS
26  
27  IF LEN(@zip_code) = 5 AND @zip_code NOT LIKE '%[^0-9]%' AND (SELECT COUNT(*) FROM Podaci WHERE first_name = @first_name AND last_name = @last_name AND city = @city AND zip_code = @zip_code AND phone_number = @phone_number) < 1
28    BEGIN TRY
29      INSERT INTO Podaci(first_name, last_name, city, zip_code, phone_number)    
30      VALUES(@first_name, @last_name, @city, @zip_code, @phone_number)
31    END TRY
32	BEGIN CATCH
33	RAISERROR('Error Inserting data',16,1) WITH LOG
34	END CATCH
35  ELSE
36	RAISERROR('That person already exists in the database or zip code is not formatted correctly', 16, 1) WITH LOG
37
38
39
40
41PROCEDURA InsertData
42-funkcionira na isti nacin kao i prosla procedura, no errori se zapisuju u druge tablice
43-errori poput dijeljenja s nulom se zapisuju u dbo.GetErrorInfo, dok se error gdje nije zadovoljen uvjet,
44zapisuje u dbo.GetErrorInfoCustom s custom porukom
45
46CREATE PROCEDURE InsertData
47    @first_name VARCHAR (50),
48    @last_name VARCHAR (50),
49    @city VARCHAR (50),
50    @zip_code VARCHAR (10),
51    @phone_number VARCHAR(20)
52AS
53
54  IF LEN(@zip_code) = 5 AND @zip_code NOT LIKE '%[^0-9]%' AND (SELECT COUNT(*) FROM Podaci WHERE first_name = @first_name AND last_name = @last_name AND city = @city AND zip_code = @zip_code AND phone_number = @phone_number) < 1
55    BEGIN TRY
56      INSERT INTO Podaci(first_name, last_name, city, zip_code, phone_number)    
57      VALUES(@first_name, @last_name, @city, @zip_code, @phone_number)
58    END TRY
59	BEGIN CATCH
60	EXEC dbo.GetErrorInfo
61	END CATCH
62  ELSE
63	EXEC dbo.GetErrorInfoCustom @ErrorMessage = "That person already exists in the database or zip is not formatted correctly"
64	
65
66PROCEDURA GetErrorInfo
67
68Create Proc [dbo].[GetErrorInfo]
69as
70begin
71insert into ExceptionLog(ErrorLine, ErrorMessage, ErrorNumber,  
72ErrorProcedure, ErrorSeverity, ErrorState,  
73DateErrorRaised  
74)  
75SELECT  
76ERROR_LINE () as ErrorLine,  
77ERROR_MESSAGE() as ErrorMessage,  
78Error_Number() as ErrorNumber,  
79Error_Procedure() as 'Proc',  
80Error_Severity() as ErrorSeverity,  
81Error_State() as ErrorState,  
82GETDATE () as DateErrorRaised 
83end 
84
85PROCEDURA GetErrorInfoCustom
86
87Create Proc [dbo].[spGetErrorInfoCustom]
88@ErrorMessage varchar (5000)
89as
90begin
91insert into ExceptionLogCustom(ErrorMessage
92)  
93SELECT  
94@ErrorMessage as ErrorMessage
95end
96
97TABLICA ExceptionLog
98-tu se ispisuje error iz procedure GetErrorInfo
99
100CREATE TABLE [dbo].[ExceptionLog](  
101[id] [int] IDENTITY(1, 1) NOT NULL,  
102[ErrorLine] [int] NULL,  
103[ErrorMessage] [nvarchar](5000) NULL,  
104[ErrorNumber] [int] NULL,  
105[ErrorProcedure] [nvarchar](128) NULL,  
106[ErrorSeverity] [int] NULL,  
107[ErrorState] [int] NULL,  
108[DateErrorRaised] [datetime] NULL  
109)  
110
111TABLICA ExceptionLogCustom
112-tu se ispisuje error iz procedure GetErrorInfoCustom
113
114CREATE TABLE [dbo].[ExceptionLogCustom](  
115[id] [int] IDENTITY(1, 1) NOT NULL, 
116[ErrorMessage] [varchar](5000) NULL
117)