October 8, 2009 at 1:36 pm
I have an ASP.NET application that provides users with the ability to set a custom sort order for child records in a particular table. The functionality is quite similar to the MyQueue feature of Netflix. When a record is deleted or the sort order is changed, I needed a means by which to maintain the values of the other records so that they're all adjusted accordingly. I wanted a solution that insures that if there are 10 child records that the range of values is from 1 to 10 regardless of order (I'm fully aware that records are not stored in the DB in any particular order).
So I developed the sp below which, base on the testing that I've done, effectively maintains the value. However, the approach seems quite bulky to me.
Is there an easier means to doing this?
I was hoping for a single, generic sp that can be called from an onUpdate or onDelete trigger to clean up the records. OnInsert isn't an issue since the value is automatically assigned.
Given that the child records are left naked (for lack of a better term) while the sp works a copy of them in a temp table, is there the possibility that another user might update the records thus breaking the code? Is there a way to lock changes to the child table while the sp is executing?
The nature of the table is such that its *very* highly unlikely that any two users would be ever be updating the same subset of child records at the same time. I could almost say that it would never happen, however I'd prefer to have the contingency accounted for just in case.
Table definition
USE [CentralEventInformation]
GO
/****** Object: Table [dbo].[Delegates] Script Date: 10/08/2009 15:17:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Delegates](
[Id] [int] IDENTITY(1000,1) NOT NULL,
[ContactId] [int] NOT NULL,
[DelegateContactId] [int] NOT NULL,
[Priority] [decimal](7, 1) NOT NULL,
[ExpirationDate] [date] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Delegates] ADD CONSTRAINT [DF_Delegates_Priority] DEFAULT ((0)) FOR [Priority]
GO
Load the table
INSERT INTO Delegates(ContactId, DelegateContactId, Priority) Values (3280, 1000, 1)
INSERT INTO Delegates(ContactId, DelegateContactId, Priority) Values (3280, 1001, 2)
INSERT INTO Delegates(ContactId, DelegateContactId, Priority) Values (3280, 1002, 3)
INSERT INTO Delegates(ContactId, DelegateContactId, Priority) Values (3280, 1003, 4)
INSERT INTO Delegates(ContactId, DelegateContactId, Priority) Values (3280, 1004, 5)
INSERT INTO Delegates(ContactId, DelegateContactId, Priority) Values (3280, 1005, 6)
INSERT INTO Delegates(ContactId, DelegateContactId, Priority) Values (3280, 1006, 7)
INSERT INTO Delegates(ContactId, DelegateContactId, Priority) Values (3280, 1007, 8)
INSERT INTO Delegates(ContactId, DelegateContactId, Priority) Values (3280, 1008, 9)
INSERT INTO Delegates(ContactId, DelegateContactId, Priority) Values (3280, 1009, 10)
The SP in question. In short, the records in the child table are placed into a temp table using a SELECT statement that has an ORDER BY. The effectively places the records in the temp table in order of priority. This was necessary since an UPDATE statement cannot specify the order in which records should be updated. Once the records are in the temp table, the Priority column is updated. Finally, the records in the child table are updated by grabbing the ID of the record and looking up its corresponding value in the temp table.
USE [CentralEventInformation]
GO
/****** Object: StoredProcedure [dbo].[sp_updateDelegatePriority] Script Date: 10/08/2009 14:27:37 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[sp_updateDelegatePriority]
(
@contactId integer,
@updatedRecord integer
)
AS
BEGIN
DECLARE @tempTable Table (Id int, RowNumber int, Priority decimal(7,1))
DECLARE @id_start decimal(7,1), @iRowCount int, @i int
DECLARE @RecordId nvarchar(50), @Priority nvarchar(50)
--Alter the updated record so that we can preserve the new priority before we begin updating the priority of the other records
UPDATE Delegates SET Priority = (Priority * - 1) WHERE Id = @updatedRecord
--Grab the records and place them into the temp table ordered by the priority
INSERT INTO @tempTable (RowNumber, Id, Priority)
SELECT ROW_NUMBER() Over(Order by Priority) as RowNum, Id, Priority FROM Delegates WHERE ContactId = @contactId ORDER BY Priority
SET @iRowCount = @@ROWCOUNT
SET @i = 0
--Show the values of the records at the point, debug only
PRINT 'RecordId' + ' ' + 'Priority'
WHILE @i <= @iRowCount
BEGIN
SELECT @RecordId = Id, @Priority = Priority FROM @tempTable WHERE RowNumber = @i
PRINT @RecordId + ' ' + @Priority
SET @i = @i + 1
END
SET @id_start = 0;
'Update the values in the temptable starting at 1, since the select statement used an ORDER BY the records are in the temp table in a specific order from lowest to highest priority
UPDATE @tempTable SET @id_start = Priority = @id_start + 1;
--Show the values of the records at the point, debug only
SET @i = 1
WHILE @i <= @iRowCount
BEGIN
SELECT @RecordId = Id, @Priority = Priority FROM @tempTable WHERE RowNumber = @i
PRINT @RecordId + ' ' + @Priority
SET @i = @i + 1
END
SET @id_start = 0;
'Use the temp table to update the records in the actual table
UPDATE DELEGATES SET Priority = (SELECT Priority FROM @tempTable WHERE Id = Delegates.Id) WHERE Id In (SELECT ID FROM @tempTable)
End
GO
October 12, 2009 at 11:53 am
The effectively places the records in the temp table in order of priority. This was necessary since an UPDATE statement cannot specify the order in which records should be updated. Once the records are in the temp table, the Priority column is updated.
Why is this necessary?
You could always just update the table with the result of the row_number() function if you insist on the priorities remaining 1-10. They should all be updated as a single transaction, but there is really no need to order the rows by priority except when you SELECT them.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 12, 2009 at 12:53 pm
Bob Hovious 24601 (10/12/2009)
The effectively places the records in the temp table in order of priority. This was necessary since an UPDATE statement cannot specify the order in which records should be updated. Once the records are in the temp table, the Priority column is updated.
Why is this necessary?
You could always just update the table with the result of the row_number() function if you insist on the priorities remaining 1-10. They should all be updated as a single transaction, but there is really no need to order the rows by priority except when you SELECT them.
The purpose of the sp is to maintain the value in the Priority column across a subset of the records in the table ensuring that the values (across the set, without regard to order) range from lowest to highest without any gaps. The intent is to provide functionality similar to the Netflix YourQueue feature.
It is my understanding that it is not possible to update records in a specific order so the statement 'UPDATE Delegates SET Priority = Row_Number() WHERE ContactID = 3280' would simply update the value in the column without regard to where the record should exist within the range when ordered by Priority. Unless there is a way to apply an ORDER BY in the UPDATE. My knowledge of SQL is such that if there is a way, its beyond me.
If the records exist as...
Id ContactId Priority
1001 3280 5
1002 3280 1
1003 3280 6
1004 3280 3
1005 3280 2
They would become...
1001 3280 1
1002 3280 2
1003 3280 3
1004 3280 4
1005 3280 5
When the desired result is
1002 3280 1
1005 3280 2
1004 3280 3
1001 3280 4
1003 3280 5
October 12, 2009 at 1:17 pm
You can use a CTE as the source for the update, like so.
declare @sampleSource table (rowid int identity(1,1),priority int) -- this would be your temp table
declare @sampleTarget table (rowID int, priority int) -- this would be the table you are going to update
-- create sample data
insert into @samplesource
select 2 union all select 1 union all select 3 union all select 16 union all select 5 union all
select 10 union all select 9 union all select 8 union all select 12 union all select 6
insert into @sampleTarget (rowid,priority)
select rowid,null from @samplesource
order by rowID
-- display before
select * from @sampleSource
select 'Before',* from @sampleTarget
-- perform update
;with src as (select rowID,row_number() over (order by priority) as newPriority from @sampleSource)
update tgt
set priority = newPriority
from @sampleTarget tgt
join src on src.rowID = tgt.rowID
-- display after
select 'After',* from @sampleTarget
order by priority
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 12, 2009 at 1:29 pm
The effectively places the records in the temp table in order of priority. This was necessary since an UPDATE statement cannot specify the order in which records should be updated.
My point was that updates do not have to occur in any particular order. You are thinking procedurally. , The rows are updated as a set and so long as the priority is right for each row, there is no problem. You don't even have to store them as 1,2,3,4,etc., because you can always use row_number() to make them sequential when you display them, like so:
declare @sampleSource table (rowid int identity(1,1),priority int) -- your temp table
declare @sampleTarget table (rowID int, priority int)
-- create sample data (note there are gaps in the priority numbers)
insert into @samplesource
select 2 union all select 1 union all select 3 union all select 16 union all select 5 union all
select 10 union all select 9 union all select 8 union all select 12 union all select 6
insert into @sampleTarget (rowid,priority)
select rowid,null from @samplesource
order by rowID
-- display before
select * from @sampleSource
select 'Before',* from @sampleTarget
-- notice we are updating here WITHOUT converting priority to sequential integers
update tgt
set priority = src.priority
from @sampleTarget tgt
join @sampleSource src on src.rowID = tgt.rowID
-- display after
select 'After',* from @sampleTarget
order by priority
-- to show as sequential numbers
select 'Sequential',rowID,row_number() over(order by priority) as priority from @sampleTarget
order by priority
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 12, 2009 at 5:53 pm
Bob Hovious 24601 (10/12/2009)
You can use a CTE as the source for the update, like so.
declare @sampleSource table (rowid int identity(1,1),priority int) -- this would be your temp table
declare @sampleTarget table (rowID int, priority int) -- this would be the table you are going to update
-- create sample data
insert into @samplesource
select 2 union all select 1 union all select 3 union all select 16 union all select 5 union all
select 10 union all select 9 union all select 8 union all select 12 union all select 6
insert into @sampleTarget (rowid,priority)
select rowid,null from @samplesource
order by rowID
-- display before
select * from @sampleSource
select 'Before',* from @sampleTarget
-- perform update
;with src as (select rowID,row_number() over (order by priority) as newPriority from @sampleSource)
update tgt
set priority = newPriority
from @sampleTarget tgt
join src on src.rowID = tgt.rowID
-- display after
select 'After',* from @sampleTarget
order by priority
My experience with SQL Server is quite minimual. What's a CTE?
October 13, 2009 at 9:09 am
Sorry about that.
CTE stands for "Common Table Expression." You can look up more details in books online (the help function), but it is basically like a view that is defined on the fly and which exists for only one query. In this respect, it functions much like a subquery, only I find them to be more readable. In the examples I sent you, the following line is a a CTE:
;with src as (select rowID,row_number() over (order by priority) as newPriority from @sampleSource)
This CTE produces a derived table called "src" which consists of two columns: rowID and newPriority. The newPriority column is created by the row_number() function and so is sequenced as consecutive integers. Change one of the examples to end with the following two lines, and you will see what the output of the src CTE looks like"
;with src as (select rowID,row_number() over (order by priority) as newPriority from @sampleSource)
select * from src
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 13, 2009 at 12:31 pm
I'll have to play with that. Pretty much as I get something up and running, I'm moving on to the next issue and then later going back to refine.
October 13, 2009 at 1:05 pm
I'll remember that the next time I take time off from MY job to help you out. 😉
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 13, 2009 at 1:25 pm
Since you're doing this in ASP, why not get your recordset from the database in the order it will be in, put it in a datagridview do whatever manipulations you need there, sorting, deletions, reordering etc are all handled by the control and then update your database as appropriate when you're done? Again you have your issues with concurrency, what if userA updated the same row as userB while they were reordering things, but you should be able to handle that by checking for changed rows prior to issuing your update.
-Luke.
October 13, 2009 at 1:49 pm
Luke L (10/13/2009)
Since you're doing this in ASP, why not get your recordset from the database in the order it will be in, put it in a datagridview do whatever manipulations you need there, sorting, deletions, reordering etc are all handled by the control and then update your database as appropriate when you're done? Again you have your issues with concurrency, what if userA updated the same row as userB while they were reordering things, but you should be able to handle that by checking for changed rows prior to issuing your update.-Luke.
The idea was to go with as clean of an approach as possible putting as much of the burden as possible onto SQL Server when it comes to the managing the data. I'm not as concerned with concurrency in this particular situation as the likelihood of the child records being updated at all is slim, let alone two people updating them at the same time. The project is a telephone directory where the child records represent a person's assistant(s) and backup(s). The Priority column is used to indicate the order in which the delegate should be called if the person isn't available. So if someone is trying to reach me and I'm not available, its clear that the next step is to call my Sales Assistant, then Sales Coordinator, etc.
October 13, 2009 at 1:59 pm
sure, ok if you really want to do it ont he SQL box I can see that, but there really isn't much in the way of heavy lifting here. I guess my point is you have to write the UI anyhow correct? Why re-invent the wheel when the datagridview already does most if not all of what you want without writing more than perhaps 15 lines of code?
Call you DAL and get a datatable that you assign to the datagridview's datasource property. Allow editing on the priority column and you're pretty much done. Drop a save button that handles passing the recordset back to your DAL for processing and you're done. You wanted a simple procedure for using as an onclick method... that seems pretty simple to me, but YMMV.
-Luke.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply