zERt2JSs

1-- UPDATE A SORT ORDER
2IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[InsertItem]' ) and OBJECTPROPERTY( id,N'IsProcedure' ) = 1)
3       DROP PROCEDURE [dbo].[InsertItem]
4GO
5
6CREATE PROCEDURE [dbo].[InsertItem]
7      @Id int,
8      @NewVal int,
9      @Hint varchar(50 )
10       AS
11BEGIN
12       DECLARE @max int
13       SELECT @max = MAX( SortOrder) FROM #OrderTest
14       -- GT MAX VALUE
15       SET @NewVal = CASE
16             WHEN @max< @NewVal
17             THEN @max + 1 ELSE @NewVal
18       END
19                        
20       --DOES NOT EXIST
21       IF NOT EXISTS( SELECT TOP 1 * FROM #OrderTest WHERE @Id=Id )
22             BEGIN
23                   -- OPEN GAP
24                   UPDATE #OrderTest
25                   SET
26                        SortOrder = ( SortOrder + 1 ),
27                        Hint = '+1n'
28                   WHERE SortOrder >= @NewVal
29                   -- INSERT NEW ITEM INTO GAP
30                   INSERT INTO #OrderTest ( SortOrder, Accessed , Hint) VALUES( @NewVal , GETDATE (), @Hint)
31             END
32       ELSE
33             BEGIN
34                   IF NOT EXISTS(
35                         SELECT TOP 1 * FROM #OrderTest WHERE @Id=Id AND @NewVal=SortOrder )
36                         -- EXISTS and MOVE REQUIRED
37                         BEGIN
38                         DECLARE @OldVal int
39                         SELECT @OldVal= SortOrder FROM #OrderTest WHERE @Id=Id
40                         -- OPEN GAP
41                               UPDATE #OrderTest
42                               SET SortOrder = (SortOrder + 1),
43                                    Hint = Hint +   '+1'
44                               WHERE SortOrder >= @NewVal
45                                AND SortOrder < @OldVal
46                               
47                              
48                               UPDATE #OrderTest
49                               SET SortOrder = (SortOrder - 1),
50                                    Hint = Hint + '-1'
51                               WHERE SortOrder <= @NewVal
52                                AND SortOrder > @OldVal
53                               
54                              
55                               -- INSERT NEW ITEM INTO GAP
56                               UPDATE #OrderTest
57                                     SET SortOrder = @NewVal,
58                                          Accessed = GETDATE (),
59                                          Hint = Hint + ' - ' + @Hint
60                                     WHERE @Id = Id
61                              
62                         END
63                   ELSE -- [[[ THIS ELSE CLAUSE CAN BE REMOVED ]]]
64                         -- EXISTS and NO MOVE REQUIRED
65                         BEGIN
66                               UPDATE #OrderTest SET [Hint] = @Hint WHERE @Id = Id
67                         END
68                   -- ENDIF
69             END
70       -- ENDIF
71END
72GO
73
74/*setup */
75CREATE TABLE #OrderTest (
76Id            int  IDENTITY (1, 1),
77SortOrder int,
78Hint    varchar(50 ) DEFAULT '',
79Accessed  DateTime )
80INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 1, GETDATE() )
81INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 2, GETDATE() )
82INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 3, GETDATE() )
83INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 4, GETDATE() )
84INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 5, GETDATE() )
85EXEC InsertItem 1 , 1 , 'NotMove-First'
86SELECT * FROM #OrderTest Order By SortOrder
87DROP TABLE #OrderTest
88GO
89
90CREATE TABLE #OrderTest (
91Id            int  IDENTITY (1, 1),
92SortOrder int,
93Hint    varchar(50 ) DEFAULT '',
94Accessed  DateTime )
95INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 1, GETDATE() )
96INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 2, GETDATE() )
97INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 3, GETDATE() )
98INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 4, GETDATE() )
99INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 5, GETDATE() )
100EXEC InsertItem 5 , 5 , 'NotMove-Last'
101SELECT * FROM #OrderTest Order By SortOrder
102DROP TABLE #OrderTest
103GO
104
105CREATE TABLE #OrderTest (
106Id            int  IDENTITY (1, 1),
107SortOrder int,
108Hint    varchar(50 ) DEFAULT '',
109Accessed  DateTime )
110INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 1, GETDATE() )
111INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 2, GETDATE() )
112INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 3, GETDATE() )
113INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 4, GETDATE() )
114INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 5, GETDATE() )
115EXEC InsertItem 2, 2 , 'NotMove-Inside'
116SELECT * FROM #OrderTest Order By SortOrder
117DROP TABLE #OrderTest
118GO
119
120CREATE TABLE #OrderTest (
121Id            int  IDENTITY (1, 1),
122SortOrder int,
123Hint    varchar(50 ) DEFAULT '',
124Accessed  DateTime )
125INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 1, GETDATE() )
126INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 2, GETDATE() )
127INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 3, GETDATE() )
128INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 4, GETDATE() )
129INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 5, GETDATE() )
130EXEC InsertItem -1, 1, 'NewItem-ToFirst'
131SELECT * FROM #OrderTest Order By SortOrder
132DROP TABLE #OrderTest
133GO
134
135
136CREATE TABLE #OrderTest (
137Id            int  IDENTITY (1, 1),
138SortOrder int,
139Hint    varchar(50 ) DEFAULT '',
140Accessed  DateTime )
141INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 1, GETDATE() )
142INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 2, GETDATE() )
143INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 3, GETDATE() )
144INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 4, GETDATE() )
145INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 5, GETDATE() )
146EXEC InsertItem -1, 6, 'NewItem-ToLast'
147SELECT * FROM #OrderTest Order By SortOrder
148DROP TABLE #OrderTest
149GO
150
151CREATE TABLE #OrderTest (
152Id            int  IDENTITY (1, 1),
153SortOrder int,
154Hint    varchar(50 ) DEFAULT '',
155Accessed  DateTime )
156INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 1, GETDATE() )
157INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 2, GETDATE() )
158INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 3, GETDATE() )
159INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 4, GETDATE() )
160INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 5, GETDATE() )
161EXEC InsertItem -1, 7, 'NewItem-ToPastLast'
162SELECT * FROM #OrderTest Order By SortOrder
163DROP TABLE #OrderTest
164GO
165
166CREATE TABLE #OrderTest (
167Id            int  IDENTITY (1, 1),
168SortOrder int,
169Hint    varchar(50 ) DEFAULT '',
170Accessed  DateTime )
171INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 1, GETDATE() )
172INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 2, GETDATE() )
173INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 3, GETDATE() )
174INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 4, GETDATE() )
175INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 5, GETDATE() )
176EXEC InsertItem -1, 5, 'NewItem-ToSecondLast'
177SELECT * FROM #OrderTest Order By SortOrder
178DROP TABLE #OrderTest
179GO
180
181CREATE TABLE #OrderTest (
182Id            int  IDENTITY (1, 1),
183SortOrder int,
184Hint    varchar(50 ) DEFAULT '',
185Accessed  DateTime )
186INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 1, GETDATE() )
187INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 2, GETDATE() )
188INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 3, GETDATE() )
189INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 4, GETDATE() )
190INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 5, GETDATE() )
191EXEC InsertItem -1, 3, 'NewItem-ToMiddle'
192SELECT * FROM #OrderTest Order By SortOrder
193DROP TABLE #OrderTest
194GO
195
196CREATE TABLE #OrderTest (
197Id            int  IDENTITY (1, 1),
198SortOrder int,
199Hint    varchar(50 ) DEFAULT '',
200Accessed  DateTime )
201INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 1, GETDATE() )
202INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 2, GETDATE() )
203INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 3, GETDATE() )
204INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 4, GETDATE() )
205INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 5, GETDATE() )
206EXEC InsertItem 1, 3 , 'Exists-FirstToMiddle'
207SELECT * FROM #OrderTest Order By SortOrder
208DROP TABLE #OrderTest
209GO
210
211CREATE TABLE #OrderTest (
212Id            int  IDENTITY (1, 1),
213SortOrder int,
214Hint    varchar(50 ) DEFAULT '',
215Accessed  DateTime )
216INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 1, GETDATE() )
217INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 2, GETDATE() )
218INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 3, GETDATE() )
219INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 4, GETDATE() )
220INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 5, GETDATE() )
221EXEC InsertItem 3, 1 , 'Exists-MiddleToFirst'
222SELECT * FROM #OrderTest Order By SortOrder
223DROP TABLE #OrderTest
224GO
225
226CREATE TABLE #OrderTest (
227Id            int  IDENTITY (1, 1),
228SortOrder int,
229Hint    varchar(50 ) DEFAULT '',
230Accessed  DateTime )
231INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 1, GETDATE() )
232INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 2, GETDATE() )
233INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 3, GETDATE() )
234INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 4, GETDATE() )
235INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 5, GETDATE() )
236EXEC InsertItem 3, 5 , 'Exists-MiddleToLast'
237SELECT * FROM #OrderTest Order By SortOrder
238DROP TABLE #OrderTest
239GO
240
241CREATE TABLE #OrderTest (
242Id            int  IDENTITY (1, 1),
243SortOrder int,
244Hint    varchar(50 ) DEFAULT '',
245Accessed  DateTime )
246INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 1, GETDATE() )
247INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 2, GETDATE() )
248INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 3, GETDATE() )
249INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 4, GETDATE() )
250INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 5, GETDATE() )
251EXEC InsertItem 5, 3 , 'Exists-LastToMiddle'
252SELECT * FROM #OrderTest Order By SortOrder
253DROP TABLE #OrderTest
254GO
255
256CREATE TABLE #OrderTest (
257Id            int  IDENTITY (1, 1),
258SortOrder int,
259Hint    varchar(50 ) DEFAULT '',
260Accessed  DateTime )
261INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 1, GETDATE() )
262INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 2, GETDATE() )
263INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 3, GETDATE() )
264INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 4, GETDATE() )
265INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 5, GETDATE() )
266EXEC InsertItem 1, 5 , 'Exists-FirstToLast'
267SELECT * FROM #OrderTest Order By SortOrder
268DROP TABLE #OrderTest
269GO
270
271CREATE TABLE #OrderTest (
272Id            int  IDENTITY (1, 1),
273SortOrder int,
274Hint    varchar(50 ) DEFAULT '',
275Accessed  DateTime )
276INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 1, GETDATE() )
277INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 2, GETDATE() )
278INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 3, GETDATE() )
279INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 4, GETDATE() )
280INSERT INTO #OrderTest ( SortOrder, Accessed ) Values ( 5, GETDATE() )
281EXEC InsertItem 5, 1 , 'Exists-LastToFirst'
282SELECT * FROM #OrderTest Order By SortOrder
283DROP TABLE #OrderTest
284GO
285--INSERT INTO #OrderTest (SortOrder, Accessed, Hint) VALUES( 1, GETDATE(), @Hint )