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 Monday, February 7, 2011 9:29 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, July 24, 2015 8:51 AM
Points: 80, Visits: 424
The added complexity is because my procedure always adjusts the sequence number so that it is continuous and zero based. Your proc works fine but you won't end up with continuous sequence numbers starting at zero. If you had say thirty items in the list and you randomly deleted fifteen, you would still have gaps between your sequence numbers (as you may have deleted all the rows sequenced between 5 and 15). Your proc would indeed get the sequencing correct but still with the gaps in the numbers, whereas mine would remove the gaps. Basically, mine takes the actual row count into effect, so you get back a sequence number range equal to zero to the row count -1. Admitedly this may be a bit over the top but I hate gaps in sequence numbers and know these will occur due to deletes. So I don't just act on the existing sequence numbers, I do the required move and then adjust them to be continuous.

Good looking proc though. Nice to see classy SQL.

Cheers, James


James
MCM [@TheSQLPimp]
Post #1059670
Posted Monday, February 7, 2011 9:30 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, July 24, 2015 8:51 AM
Points: 80, Visits: 424
The added complexity is because my procedure always adjusts the sequence number so that it is continuous and zero based. Your proc works fine but you won't end up with continuous sequence numbers starting at zero. If you had say thirty items in the list and you randomly deleted fifteen, you would still have gaps between your sequence numbers (as you may have deleted all the rows sequenced between 5 and 15). Your proc would indeed get the sequencing correct but still with the gaps in the numbers, whereas mine would remove the gaps. Basically, mine takes the actual row count into effect, so you get back a sequence number range equal to zero to the row count -1. Admitedly this may be a bit over the top but I hate gaps in sequence numbers and know these will occur due to deletes. So I don't just act on the existing sequence numbers, I do the required move and then adjust them to be continuous.

Good looking proc though. Nice to see classy SQL.

Cheers, James


James
MCM [@TheSQLPimp]
Post #1059674
Posted Monday, February 7, 2011 9:40 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, July 24, 2015 8:51 AM
Points: 80, Visits: 424
Blimey, well, it proves that old problems never actually go away but the code to deal with them just evolves.

Current reading up on the Merge command.... Thank you for pointing me at this. I'm never afraid to be shown a better solution; indeed, if this never happenned then I'd never learn anything!

Cheers, James



James
MCM [@TheSQLPimp]
Post #1059687
Posted Monday, February 7, 2011 10:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 7:53 PM
Points: 2, Visits: 59
I have implemented a solution very similar to the one in Ten Centuries post for a similar problem. I think it is simpler, more elegant and easier to read than your solution. Please don’t read that as insult as I think that your solution is more robust for the reason that you described and can understand the corresponding increased complexity.

However, it would probably be beneficial to anyone reading if you had included a description of that added functionality in the original post. Also, as you have written your proc, there is no way to take advantage of that functionality so the reason you are doing it the way you are can’t really be capitalized upon. The setTodoSequence proc only provides a way to change the sequence, not delete a block of items, so there is no reason to have that stored procedure be more complicated than a simpler update statement with the appropriate condition and range testing.

I do think there is good information here and I have benefitted from seeing row_number() used in this way.
Post #1059760
Posted Monday, February 7, 2011 12:18 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, July 24, 2015 8:51 AM
Points: 80, Visits: 424
Thank you for the kind comments, they are all very useful. This is the first article I have tried to write and judging by the feedback, I'm on the right track. Believe me, I take nothing as an insult, it's all constructive! I can see by reading the article back how I could have explained the extra complexity and made the whole thing easier to read.

I have another article being published on Thursday, so do please read that as well and give me any feedback - good or bad!

Cheers, James


James
MCM [@TheSQLPimp]
Post #1059836
Posted Monday, February 7, 2011 2:27 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, May 27, 2015 10:01 AM
Points: 1,322, Visits: 1,094
The added complexity is because my procedure always adjusts the sequence number so that it is continuous and zero based.

James,
While it is true that your routine does this, it seems to me more proper to put that aspect of your code into the Delete routine instead of renumbering everything every time you change the priority on one task.
--
JimFive
Post #1059912
Posted Monday, February 7, 2011 2:57 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, July 24, 2015 8:51 AM
Points: 80, Visits: 424
James,
I would agree that maybe the delete routine would be a better place for the zero re-numbering and that this does over-complicate the routine somewhat.

James


James
MCM [@TheSQLPimp]
Post #1059934
Posted Monday, February 7, 2011 11:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 5, 2013 7:28 AM
Points: 3, Visits: 36
Cool solution for custom sequences. If you don't have immediate application needs, you could just wait for Denali (SQL 11) to go RTM and use the new Sequence Object natively in SQL Server ...
Post #1060030
Posted Tuesday, February 8, 2011 2:15 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, July 24, 2015 8:51 AM
Points: 80, Visits: 424
Oh I'm looking forward to that! Currently looking into the new datetimeoffset data type in 2008 to replace my "convert to local date via a utc hour and minute offset lookup table" function! Looks to be the way forward if you deal with data from multiple time zones. I feel amother article coming on this once I've looked into the pros and cons - as let's face it, there are always some cons! Ever tried using the xml nodes method with a very large xml blob? Not good, the old prepare doc and openxml method is way faster but you only find these things out from testing.

Thanks again for the supportive comments.

Cheers, James


James
MCM [@TheSQLPimp]
Post #1060080
Posted Tuesday, February 8, 2011 9:38 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, June 8, 2015 10:57 AM
Points: 108, Visits: 380
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
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse