Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Custom Sequence Numbering Expand / Collapse
Author
Message
Posted Tuesday, February 08, 2011 9:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 9:07 AM
Points: 106, Visits: 356
I have used a similar procedure to move records in a sequence. I won't repeat the table setup, but I need to point out that I used "1" as the starting value rather than "0" because people normally think of the top item as "1". Here is the procedure I use, formatted to work with the table in the sample.

The procedure allow the caller to specifiy either a direction ("TOP", "UP", "DOWN", or "BOTTOM") or a specific sequence number (move item x to sequence number 5).

SET	ANSI_NULLS ON
SET ANSI_PADDING ON
IF OBJECTPROPERTY(OBJECT_ID('ToDoItemMoveRecord'), 'IsProcedure') = 1
DROP
PROC ToDoItemMoveRecord
GO

CREATE
PROC ToDoItemMoveRecord
@ToDoID INTEGER,
@Direction VARCHAR(10) = NULL,
@NewSortSeq TINYINT = NULL
AS
-----------------------------------------------------------------------------------------------------------
SET NOCOUNT ON
DECLARE @ErrNum INTEGER, @ErrDesc VARCHAR(2000),
@ErrRows INTEGER, @ErrSeverity INTEGER,
@CurrentSeq TINYINT, @MaxSeq TINYINT,
@StartSeq TINYINT, @EndSeq TINYINT,
@PersonID INTEGER

BEGIN TRY
--<logic>Verify the parameters.</logic>
SET @Direction = UPPER(@Direction)

IF @NewSortSeq IS NULL
AND @Direction IS NULL
RAISERROR('Please specify either a new SortSeq or a Direction to move.', 16, 1)

IF @NewSortSeq IS NOT NULL
AND @Direction IS NOT NULL
RAISERROR('Please specify either a new SortSeq or a Direction to move, but not both.', 16, 1)

--<logic>Locate the current values from the table.</logic>
SET @PersonID =
(SELECT PersonID FROM ToDoList WHERE ToDoID = @ToDoID)
SET @CurrentSeq =
(SELECT todoSequence FROM ToDoList WHERE ToDoID = @ToDoID)
SET @MaxSeq =
(SELECT MAX(todoSequence) FROM ToDoList WHERE PersonID = @PersonID)

--<logic>If the new sort sequence is provided, determine the direction of the move.</logic>
IF @NewSortSeq IS NOT NULL
BEGIN -- Specific sort
IF @NewSortSeq < 1
BEGIN
RAISERROR('The requested sort sequence cannot be less than 1.', 16, 1)
END
ELSE IF @NewSortSeq > @MaxSeq
BEGIN
RAISERROR('The requested sort sequence cannot be more than the current maximum in the table.', 16, 1)
END
ELSE IF @NewSortSeq = 1
BEGIN
SET @Direction = 'TOP'
SET @StartSeq = @NewSortSeq
SET @EndSeq = @CurrentSeq
END
ELSE IF @NewSortSeq < @CurrentSeq
BEGIN
SET @Direction = 'UP'
SET @StartSeq = @NewSortSeq
SET @EndSeq = @CurrentSeq
END
ELSE IF @NewSortSeq = @MaxSeq
BEGIN
SET @Direction = 'BOTTOM'
SET @StartSeq = @CurrentSeq
SET @EndSeq = @NewSortSeq
END
ELSE IF @NewSortSeq > @CurrentSeq
BEGIN
SET @Direction = 'DOWN'
SET @StartSeq = @CurrentSeq
SET @EndSeq = @NewSortSeq
END
ELSE
BEGIN
RAISERROR('The necessary move could not be determined.', 16, 1)
END
END -- Specific sort
--<logic>If a move direction is requested, determine the new sort sequence.</logic>
ELSE
BEGIN -- Direction requested

IF @Direction = 'TOP'
BEGIN
SET @StartSeq = 1
SET @EndSeq = @CurrentSeq
SET @NewSortSeq = @StartSeq
END
ELSE IF @Direction = 'UP'
BEGIN
SET @StartSeq = @CurrentSeq - 1
SET @EndSeq = @CurrentSeq
SET @NewSortSeq = @StartSeq
END
ELSE IF @Direction = 'DOWN'
BEGIN
SET @StartSeq = @CurrentSeq
SET @EndSeq = @CurrentSeq + 1
SET @NewSortSeq = @EndSeq
END
ELSE IF @Direction = 'BOTTOM'
BEGIN
SET @StartSeq = @CurrentSeq
SET @EndSeq = @MaxSeq
SET @NewSortSeq = @EndSeq
END

END -- Direction requested

--<logic>Check to be sure the requested move can be made; raise an error if it cannot.</logic>
IF @NewSortSeq IS NULL
BEGIN -- Invalid record
RAISERROR('Record not found.', 16, 1)
END -- Invalid record

IF @CurrentSeq = 1 AND @Direction IN ('UP', 'TOP')
BEGIN -- Invalid record
RAISERROR('The record is currently at the top and cannot move up.', 16, 1)
END -- Invalid record

IF @CurrentSeq = @MaxSeq AND @Direction IN ('DOWN', 'BOTTOM')
BEGIN -- Invalid record
RAISERROR('The record is currently at the bottom and cannot move down.', 16, 1)
END -- Invalid record

IF @NewSortSeq = @CurrentSeq
BEGIN -- Invalid record
RAISERROR('The record is currently at the requested position and cannot be moved.', 16, 1)
END -- Invalid record

/* -- Testing
select @Direction AS '@Direction',
@NewSortSeq AS '@NewSortSeq',
@Currentseq AS '@CurrentSeq',
@MaxSeq AS '@MaxSeq',
@StartSeq AS '@StartSeq',
@EndSeq AS '@EndSeq'
*/
--<logic> - Move the necessary records.</logic>
UPDATE ToDoList
SET todoSequence = CASE
WHEN todoSequence = @CurrentSeq THEN @NewSortSeq
WHEN @Direction IN ('TOP', 'UP') THEN todoSequence + 1
WHEN @Direction IN ('DOWN', 'BOTTOM') THEN todoSequence - 1
ELSE NULL END -- Null is used so that an error will occur if the three cases above are not valid.
WHERE todoSequence BETWEEN @StartSeq AND @EndSeq

END TRY
BEGIN CATCH
SET @ErrNum = ERROR_NUMBER() -- Needed for return value
SET @ErrSeverity = ERROR_SEVERITY() -- Needed to bubble up the same severity
SET @ErrDesc = dbo.FormatErrDesc()
GOTO NonTranError
END CATCH

RETURN

NonTranError:
RAISERROR (@ErrDesc, @ErrSeverity, 1)
RETURN @ErrNum -- Return the error number

GO

GRANT EXECUTE ON ToDoItemMoveRecord TO Public
GO

Post #1060376
Posted Tuesday, February 08, 2011 9:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 10, 2014 9:03 AM
Points: 103, Visits: 289
James,

I really liked this article. I especially appreciated how you spelled out the logic in text. Many articles make you puzzle it out in the code. Thank you.

Amy
Post #1060390
Posted Tuesday, February 08, 2011 9:55 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, April 04, 2014 7:39 AM
Points: 59, Visits: 337
I think I used zero as the starting sequence due to all the C# programming I have been doing lately - and the hardened C# community would surely spit on anyone starting a sequence or array with 1 - the mere thought of it!!!

After reading the forum I do have started to come to the conclusion that my procedure was maybe overkill in the terms of the scope of what it was trying to do - i.e. both moving the record to the desired position and renumbering the whole sequence in the same process. I am now of a mind to do the gap removal and resequencing to zero in the delete procedure and then change the proc to be more like yours and another one that was posted.

I don't have as much validation logic in as yours though, as when I last implemented this the front-end code took care of that. Nice proc though, rare nowadays to see such good error checking! Thanks for taking the time to read the article.



James
MCM [@TheSQLPimp]
Post #1060391
Posted Tuesday, February 08, 2011 9:59 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, April 04, 2014 7:39 AM
Points: 59, Visits: 337
Thank you Amy and apologies for the initial typo (which has now been corrected). I have learned a lot about the article writing style from the comments and the whole process has been beneficial for me. I have another one published on Thursday showing how to auto-generate stored procedures and I have triple-checked the attached code to make sure it is 100% correct!

Again, thanks for taking the time to comment, I really appreciate it.



James
MCM [@TheSQLPimp]
Post #1060395
Posted Tuesday, February 08, 2011 1:21 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 12:26 AM
Points: 77, Visits: 299
fahey.jonathan (2/8/2011)
I have used a similar procedure to move records in a sequence....


Jonathan's code (above) made sense to me. But I couldn't really figure out what was going on in the original article's case statement. It makes sense conceptually, but the specifics of the code were too funky for me to parse. Partly this is because I'm new to this stuff, but also partly because the Jonathan's code is so much more nicely formatted and easy to reconcile. I'd humbly request that the article author reformat his code so it's more readable.

And also, please note that as of the time of this posting, the incorrect column name ("todoGroup") is still specified in the original article.



"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura
Post #1060597
Posted Tuesday, February 08, 2011 2:06 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, April 04, 2014 7:39 AM
Points: 59, Visits: 337
Unfortunately the funky code is very efficient. The problem with stored procedures with a lot of IF statements in is that they do not cache well, as the compiler stores a different plan each time a new path is followed, making for poor reuse. The code is complicated to make it efficient. Apolgies if it is hard to follow. I will change the typo as soon as I can.

Cheers, James


James
MCM [@TheSQLPimp]
Post #1060642
Posted Tuesday, February 08, 2011 2:55 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 9:07 AM
Points: 106, Visits: 356
The original code updates every record for the person, irrespective of whether it changes or not. I think updating only the rows that change makes up for any inefficiency with an "IF" statement.

I put the two procedures in the database and looked at the execution plans. It is not always an accurate reflection of execution time, I know, but it does provide very useful information. The original procedure was expected to take 66% of the time and the revised procedure was expected to take 34%. The compiler seems to think the "IF" statements are not a significant detriment.

I have one other thing to note. The original procedure accepts as parameters both the PersonID and the ToDoID. The PersonID is dependent on the ToDoID, so either the procedure should not accept the PersonID as a parameter and just look it up, or it should validate that the PersonID passed was the correct PersonID for the ToDoID.
Post #1060679
Posted Tuesday, February 08, 2011 3:10 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, April 04, 2014 7:39 AM
Points: 59, Visits: 337
Again, this is because my procedure also removes gaps in the sequence, hence why it updates all the records. If you try my procedure with different parameters and look at the cache hits it will always hit, whereas the other one will recompile for different parameters combinations. As I stated in an earlier post, I will remove the resequencing to remove gaps and restate at zero into the delete routine, enabling my procedure to also work on a range and drop drastically in complexity. Apologies if you have not read back this far. My procedure suffered by being to all-encapsulating, as if you had a broken sequence due to deletes of say 1,5,7,11,13, mine would set this back to 0,1,2,3,4, whereas the others wouldn't - hence why it is complex and hence why it updates all records.

Cheers, james


James
MCM [@TheSQLPimp]
Post #1060693
Posted Tuesday, February 08, 2011 3:23 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, April 04, 2014 7:39 AM
Points: 59, Visits: 337
Apolgies, didn't answer the todoid and personid question. I'd expect the business/application layer to do this, as if the application was passing me a todoid for the wrong personid then I'd be very concerned. Some business logic in the database layer is good but validation such as this should be done at a higher level. Same goes for "this is already the first one" etc.

Cheers, James


James
MCM [@TheSQLPimp]
Post #1060702
Posted Tuesday, February 08, 2011 3:50 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:49 PM
Points: 20,462, Visits: 14,092
thanks for the article.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1060717
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse