· 8 years ago · Aug 25, 2018, 09:32 PM
1Microsoft SQL Server 2008 cursor
2DECLARE @COMP_NUM AS VARCHAR(100) = '4'
3DECLARE @COMP_NAME AS VARCHAR(100) = 'TAGASTE'
4DECLARE @STREET_NUM AS VARCHAR(100) = '150'
5DECLARE @STREET_NAME AS VARCHAR(100) = 'WILLSON'
6DECLARE @STREET_TYPE AS INT = 1
7DECLARE @SUB AS VARCHAR(100) = 'FAIRLANDS'
8DECLARE @CITY AS VARCHAR(100) = 'JOHANNESBURG'
9DECLARE @HOMEPHONE AS VARCHAR(100) = '0112355566'
10DECLARE @EXISTS AS INT
11DECLARE @ADD_ID AS INT
12
13SET @EXISTS = (SELECT COUNT(ID) FROM CARETEAMZ..ADDRESS_BOOK WHERE HOME_PHONE=@HOMEPHONE
14AND COMPLEX_NUMBER = @COMP_NUM
15AND COMPLEX_NAME = @COMP_NAME
16AND STREET_NUMBER = @STREET_NUM
17AND STREET_NAME = @STREET_NAME
18AND STREET_TYPE = @STREET_TYPE
19AND SUBURB = @SUB
20AND CITY = @CITY)
21PRINT ('COUNT ROWS: ' + CONVERT(CHAR(6),@EXISTS))
22
23IF (CONVERT(INT,@EXISTS) > 0)
24BEGIN
25 PRINT ('RECORD EXISTS')
26 SET @ADD_ID = (SELECT ID FROM CARETEAMZ..ADDRESS_BOOK WHERE HOME_PHONE = @HOMEPHONE
27 AND COMPLEX_NUMBER = @COMP_NUM
28 AND COMPLEX_NAME = @COMP_NAME
29 AND STREET_NUMBER = @STREET_NUM
30 AND STREET_NAME = @STREET_NAME
31 AND STREET_TYPE = @STREET_TYPE
32 AND SUBURB = @SUB
33 AND CITY = @CITY)
34 PRINT ('ADDRESS ID: ' + CONVERT(CHAR(6),@ADD_ID))
35END
36ELSE
37BEGIN
38 PRINT ('RECORD DOES NOT EXIST')
39 INSERT INTO CARETEAMZ..ADDRESS_BOOK (HOME_PHONE, COMPLEX_NUMBER, COMPLEX_NAME, STREET_NUMBER, STREET_NAME,STREET_TYPE, SUBURB, CITY)
40 VALUES (@HOMEPHONE, @COMP_NUM,@COMP_NAME, @STREET_NUM,@STREET_NAME,@STREET_TYPE,@SUB,@CITY)
41
42 SET @ADD_ID = (SELECT ID FROM CARETEAMZ..ADDRESS_BOOK WHERE HOME_PHONE = @HOMEPHONE
43 AND COMPLEX_NUMBER = @COMP_NUM
44 AND COMPLEX_NAME = @COMP_NAME
45 AND STREET_NUMBER = @STREET_NUM
46 AND STREET_NAME = @STREET_NAME
47 AND STREET_TYPE = @STREET_TYPE
48 AND SUBURB = @SUB
49 AND CITY = @CITY)
50 PRINT ('ADDRESS ID: ' + CONVERT(char(6),@ADD_ID))
51END
52
53DECLARE @COMP AS VARCHAR(500)
54DECLARE @COMP_LENGTH AS INT
55DECLARE @COMP_INDEX AS INT
56DECLARE @COMP_NUM AS VARCHAR(50)
57DECLARE @COMP_NAME AS VARCHAR(100)
58DECLARE @STREET AS VARCHAR(500)
59DECLARE @STREET_LENGTH AS INT
60DECLARE @STREET_INDEX AS INT
61DECLARE @STREET_NUM AS VARCHAR(50)
62DECLARE @STREET_NAME AS VARCHAR(100)
63DECLARE @STREET_VALUE AS VARCHAR(200)
64DECLARE @STREET_TYPE AS INT
65DECLARE @SUB AS VARCHAR(100)
66DECLARE @CITY AS VARCHAR(100)
67DECLARE @HOMEPHONE AS VARCHAR(100)
68SET @CITY = 'JOHANNESBURG'
69
70DECLARE ADD_CURSOR CURSOR FOR SELECT [HOME],[COMPLEX], [STREET] FROM #TEMPADD
71OPEN ADD_CURSOR
72FETCH NEXT FROM ADD_CURSOR INTO @HOMEPHONE,@COMP,@STREET;
73 WHILE @@FETCH_STATUS = 0
74 BEGIN
75 --COMPLEX DETAILS
76 PRINT('-- COMPLEX DETAILS --')
77 PRINT(@COMP)
78 SET @COMP_INDEX = CHARINDEX(',',@COMP)
79 PRINT(@COMP_INDEX)
80 IF (@COMP_INDEX > 0)
81 BEGIN
82 PRINT('COMPLEX TRUE')
83 SET @COMP_NUM = LTRIM(RTRIM((SUBSTRING(@COMP,0,@COMP_INDEX))))
84 SET @COMP_NAME = LTRIM(RTRIM((SUBSTRING(@COMP,@COMP_INDEX+1,LEN(@COMP)))))
85 END
86 ELSE
87 BEGIN
88 PRINT('COMPLEX FALSE')
89 SET @COMP_NUM = NULL
90 SET @COMP_NAME = NULL
91 END
92 PRINT('COMPLEX NUMBER: ' + CONVERT(VARCHAR(50),@COMP_NUM))
93 PRINT('COMPLEX NAME: ' + @COMP_NAME)
94
95 -- STREET ADDRESS
96 PRINT('-- STREET NAME --')
97 PRINT(@STREET)
98 SET @STREET_INDEX = CHARINDEX(',',@STREET)
99 PRINT(@STREET_INDEX)
100 IF (@STREET_INDEX > 0)
101 BEGIN
102 PRINT('STREET TRUE')
103 SET @STREET_NUM = LTRIM(RTRIM((SUBSTRING(@STREET,0,@STREET_INDEX))))
104 SET @STREET = (SUBSTRING(@STREET,@STREET_INDEX+1,LEN(@STREET)))
105 PRINT(@STREET)
106 SET @STREET_INDEX = CHARINDEX(',',@STREET)
107 SET @STREET_NAME = LTRIM(RTRIM((SUBSTRING(@STREET,0,CHARINDEX(' ',@STREET)))))
108 SET @SUB = LTRIM(RTRIM((SUBSTRING(@STREET,@STREET_INDEX+1,LEN(@STREET)))))
109 SET @STREET_VALUE = SUBSTRING(@STREET,0,CHARINDEX(',',@STREET))
110 SET @STREET_TYPE = (SELECT ID FROM CARETEAMZ..STREET_TYPE WHERE STREET_TYPE = RTRIM(LTRIM((SUBSTRING(@STREET_VALUE,LEN(@STREET_VALUE)-CHARINDEX(' ',REVERSE(@STREET_VALUE))+2,LEN(@STREET_VALUE)-CHARINDEX(',',@STREET_VALUE))))))
111 END
112 ELSE
113 BEGIN
114 PRINT('STREET FALSE')
115 SET @STREET_NUM = NULL
116 SET @STREET_NAME = NULL
117 SET @STREET_TYPE = NULL
118 SET @SUB = NULL
119 END
120 PRINT('STREET NUMBER: ' + @STREET_NUM)
121 PRINT('STREET NAME: ' + @STREET_NAME)
122 PRINT('STREET TYPE: ' + CONVERT(VARCHAR(10),@STREET_TYPE))
123 PRINT('SUBURB: ' + @SUB)
124
125 --CHECK IF THE ADDRESS / PHONE NUMBER ALREADY EXISTS IN THE DB
126 DECLARE @ADD_ID AS INT
127 DECLARE @EXISTS AS INT
128
129 SET @EXISTS = (SELECT COUNT(ID) FROM CARETEAMZ..ADDRESS_BOOK WHERE HOME_PHONE = @HOMEPHONE
130 AND COMPLEX_NUMBER = @COMP_NUM
131 AND COMPLEX_NAME = @COMP_NAME
132 AND STREET_NUMBER = @STREET_NUM
133 AND STREET_NAME = @STREET_NAME
134 AND STREET_TYPE = @STREET_TYPE
135 AND SUBURB = @SUB
136 AND CITY = @CITY)
137 PRINT ('COUNT ROWS: ' + CONVERT(CHAR(6),@EXISTS))
138 IF (@EXISTS > 0)
139 BEGIN
140 PRINT ('RECORD EXISTS')
141 SET @ADD_ID = (SELECT ID FROM CARETEAMZ..ADDRESS_BOOK WHERE HOME_PHONE = @HOMEPHONE
142 AND COMPLEX_NUMBER = @COMP_NUM
143 AND COMPLEX_NAME = @COMP_NAME
144 AND STREET_NUMBER = @STREET_NUM
145 AND STREET_NAME = @STREET_NAME
146 AND STREET_TYPE = @STREET_TYPE
147 AND SUBURB = @SUB
148 AND CITY = @CITY)
149
150 PRINT ('ADDRESS ID: ' + CONVERT(CHAR(6),@ADD_ID))
151 END
152 ELSE
153 BEGIN
154 PRINT ('RECORD DOES NOT EXIST')
155 INSERT INTO CARETEAMZ..ADDRESS_BOOK (HOME_PHONE, COMPLEX_NUMBER, COMPLEX_NAME, STREET_NUMBER, STREET_NAME,STREET_TYPE, SUBURB, CITY)
156 VALUES (@HOMEPHONE, @COMP_NUM,@COMP_NAME, @STREET_NUM,@STREET_NAME,@STREET_TYPE,@SUB,@CITY)
157
158 SET @ADD_ID = (SELECT ID FROM CARETEAMZ..ADDRESS_BOOK WHERE HOME_PHONE = @HOMEPHONE
159 AND COMPLEX_NUMBER = @COMP_NUM
160 AND COMPLEX_NAME = @COMP_NAME
161 AND STREET_NUMBER = @STREET_NUM
162 AND STREET_NAME = @STREET_NAME
163 AND STREET_TYPE = @STREET_TYPE
164 AND SUBURB = @SUB
165 AND CITY = @CITY)
166
167 PRINT ('ADDRESS ID: ' + CONVERT(char(6),@ADD_ID))
168
169 END
170
171 --FETCH NEXT
172 FETCH NEXT FROM ADD_CURSOR INTO @HOMEPHONE,@COMP, @STREET;
173 END
174CLOSE ADD_CURSOR;
175DEALLOCATE ADD_CURSOR;
176
177CREATE TABLE #TEMPADD
178(
179 HOME VARCHAR(100) NULL,
180 COMPLEX VARCHAR(100) NULL,
181 STREET VARCHAR(100) NULL,
182)
183
184INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES ('011 679 6787', '32,Tagaste', '150,Willson Street, Land')
185INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES ('011 679 1909', NULL, '29,Bunkara Street, Rio')
186INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES ('011 689 2630', NULL, '275,Kings Lynne Road, Glen')
187INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES (NULL, NULL, '275,Kings Lynne Road, Glen')
188INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES ('011 799 5917', '5,The Vineyard', '45,Hilary Road, Ridge')
189INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES (NULL, '5,The Vineyard', '45,Hilary Road, Ridge')
190INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES ('011 679 5857', NULL, '11A,Alexandra Street, Florida')
191INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES ('011 679 5857', NULL, '11A,Alexandra Street, Florida')
192INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES ('011 679 3225', NULL, '752, Without Avenue, Weltevreden')
193INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES ('011 679 8909', NULL, '18,Smit Street,Land')
194INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES ('011 679 8909', '512,Athenian View', '158,Smit Street, Fairland')
195INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES (NULL, '741,Eagle Trace Landing', 'Eagle Canyon Golf Estate, Honey')
196INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES (NULL, '741,Eagle Trace Landing', 'Eagle Canyon Golf Estate, Honey')
197INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES (NULL, '741,Eagle Trace Landing', 'Eagle Canyon Golf Estate, Honey')
198INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES (NULL, '741,Eagle Trace Landing', 'Eagle Canyon Golf Estate, Honey')
199INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES (NULL, '741,Eagle Trace Landing', 'Eagle Canyon Golf Estate, Honey')
200INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES (NULL, '741,Eagle Trace Landing', 'Eagle Canyon Golf Estate, Honey')
201INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES (NULL, NULL, '106A,3rd Avenue, Land')
202
203CREATE TABLE [dbo].[ADDRESS_BOOK](
204 [ID] [int] IDENTITY(1,1) NOT NULL,
205 [PoBox] [varchar](50) NULL,
206 [Complex_Number] [varchar](50) NULL,
207 [Complex_Name] [varchar](100) NULL,
208 [Street_Number] [varchar](50) NULL,
209 [Street_Name] [varchar](100) NULL,
210 [Street_Type] [int] NULL,
211 [Suburb] [varchar](100) NULL,
212 [City] [varchar](50) NULL,
213 [Code] [varchar](50) NULL,
214 [Home_Phone] [varchar](50) NULL
215) ON [PRIMARY]
216
217If @Exists exists, then @Add_ID exists.
218
219If @Add_ID exists, then @Add_ID exists.
220
221SET @ADD_ID = (SELECT ID FROM CARETEAMZ..ADDRESS_BOOK WHERE HOME_PHONE = @HOMEPHONE AND ...)
222IF @ADD_ID IS NOT NULL
223BEGIN
224 PRINT(...)
225END
226ELSE...