I had a brainstorm of an idea for a group of articles on my blog a couple of weeks ago and am finally getting around to putting it together. The idea came about while I was struggling with a problem at work and trying to meet the requirements in the code. As the title states, this group is going to be about things done in SQL server that I would normally avoid.
The first article came about while trying to find a solution to some business requirements. The requirements had an average to be calculated and then a revised average if certain criteria were met. Then it was required to move things from one group to another group depending on a series of criteria. When I first looked at these requirements, I thought I would need several cursors. As it turns out, I had to implement just one cursor. I could find no way of getting away from using a cursor for this one requirement. The essence of it was that I had two variables. One variable was the number of items to move and the second variable was the person to whom the items needed to be moved. Not only did it require those two variables, but there was the additional requirement that it be randomized.
First, let’s take a look at how I did the randomizing. I took care of this requirement by dumping all of my prep data into a temp table that had a default on a column explicitly to hold a random value. Here is the code for that table.
[codesyntax lang=”tsql”]
Create Table LeadRedist_PhaseThree2 ( LeadRedistIDInt Identity(1,1) Primary Key Clustered, ActivityOwneruniqueidentifier, OppOwneruniqueidentifier, OpportunityIDuniqueidentifier, ownershipbegindateDatetime, ScheduledDateDatetime, DispositionVarchar(30), RandomizerFloat DEFAULT RAND(CHECKSUM(NEWID())) * 10000, RowNumInt, OwnerFullNameVarchar(50), OppAuditDetailIDuniqueidentifier DEFAULT NEWSEQUENTIALID(), ActAuditDetailIDuniqueidentifier DEFAULT NEWSEQUENTIALID(), OppAuditIDuniqueidentifier DEFAULT NEWSEQUENTIALID(), ActAuditIDuniqueidentifier DEFAULT NEWSEQUENTIALID(), NewOwneruniqueidentifier )
[/codesyntax]
Using this table, I can just run a set-based insert to populate the table and thus end up with random values in the “Randomizer” column due to the default constraint.
[codesyntax lang=”tsql”]
DEFAULT RAND(CHECKSUM(NEWID())) * 10000
[/codesyntax]
That piece of the puzzle was fixed rather easily. Now for the next part – the confession part. I had to write a cursor. Next part of that confession – I had to look up how to write the cursor. Without going into a lot of details, this is the code of how I used a cursor to solve this particular problem.
[codesyntax lang=”tsql”]
Declare @NewUserIDUniqueIdentifier ,@OppstoRecInt Declare PhaseThree_Cursor Cursor For Select LV.SystemUserID, LV.OppstoReceive From LeadRedist_Avgs2 LV Where LV.OppstoReceive > 0 Order By LV.OppstoReceive Desc Open PhaseThree_Cursor; Fetch Next From PhaseThree_Cursor Into @NewUserID, @OppstoRec; WHILE @@FETCH_STATUS = 0 Begin With reassign as ( Select top (@OppstoRec) LP.LeadRedistID From LeadRedist_PhaseThree2 LP Where LP.NewOwner is null Order By LP.Randomizer Desc ) Update LP Set LP.NewOwner = @NewUserID From LeadRedist_PhaseThree2 LP Inner Join reassign r On LP.LeadRedistID = r.LeadRedistID Fetch Next From PhaseThree_Cursor Into @NewUserID, @OppstoRec; End Close PhaseThree_Cursor DeAllocate PhaseThree_Cursor
[/codesyntax]
Note that this cursor is a little hybrid. I am using this cursor to perform some set based actions. I am fine with that. I am happy with the random distribution and the fact that this performs well. This was done for a one off requirement that will not be used again (or at least not planned to be used again) and it met all of the requirements. I was blocked on this particular requirement by the required variables and couldn’t get past how to do this in a 100% set-based fashion. If you have any input, let me know.
Do you have any such stories to share?
