Looping without cursor not updating properly

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Looping without cursor not updating properly

39 posts, Page 1 of 4

1 2 3 4 »»»

Looping without cursor not updating properly

Rate Topic

Display Mode

Topic Options

Author

Message

Siobhan Perricone

Posted Friday, March 08, 2013 6:56 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 11:58 AM
Points: 14, Visits: 20

I got asked to not use a cursor for a loop that I'm working on so I grabbed the code here:
http://www.sql-server-performance.com/2004/operations-no-cursors/

And adjusted it to do what I need. It's not updating how I'm expecting it to. If I comment out the part where it subtracts one from the town count, then it treats everyone as if the first if statement were true. If I don't comment it out, then it treats everyone as if both the first and second are false because it is subtracting all the counts from the towncount and making it 0.

There's probably some stupid syntax thing that I'm forgetting or just not realizing I need. I'm late getting this out, and I have staff getting calls about this lottery, so I was hoping I could get some help. Thanks for any suggestions you can make that'll help me find an avenue for pursuit. (sorry for the big indenting I can't seem to get it to stop doing that)

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [survey].[sp_AwardFirewoodLots]
AS
BEGIN

                -- SET NOCOUNT ON added to prevent extra result sets from
                -- interfering with SELECT statements.
                SET NOCOUNT ON;

exec [survey].[sp_ResetPermitsCountdown]

                -- declare all variables!
                DECLARE @iReturnCode INT
                       ,@iNextRowId INT
                       ,@iCurrentRowId INT
                       ,@iLoopControl INT
                       ,@ApplicantID INT
                       ,@TownCount INT
		 			   ,@PermitsCount INT
                       ,@FirstChoice NCHAR(50)
                       ,@SecondChoice NCHAR(50)

                -- Create randomized temp table of applicants
                --DROP TABLE #RandomizedApplicants

                SELECT @iLoopControl = 1

                SELECT TOP 500 row_number() over (ORDER BY NEWID()) as iRowId
							   ,FirewoodLottery_id
                               ,choice1
                               ,choice2
                INTO #RandomizedApplicants
                FROM survey.FirewoodLottery
                ORDER BY NEWID()

                -- Initialize variables!
                SELECT @iNextRowId = MIN(iRowId)
                FROM #RandomizedApplicants

                -- Make sure the table has data.
                IF ISNULL(@iNextRowId, 0) = 0
                BEGIN
                  SELECT 'No data in found in table!'
                  RETURN
                END

                -- Retrieve the first row
                SELECT @iCurrentRowId = iRowId
                      ,@ApplicantID = FirewoodLottery_id
                      ,@FirstChoice = choice1
                     ,@SecondChoice = choice2
                FROM #RandomizedApplicants
                WHERE iRowId = @iNextRowId

                -- start the main processing loop.
                WHILE @iLoopControl = 1
                BEGIN
                                -- This is where you perform your detailed row-by-row processing.    
                                -- figure out if the first choice is full
                                SELECT @PermitsCount = [PermitsCountdown]
                                FROM survey.FirewoodTowns
                                WHERE [Town] = @FirstChoice

                                IF @PermitsCount > 0
                                BEGIN
												UPDATE survey.FirewoodLottery
                                                SET assigned = @FirstChoice
                                                   ,notes = 'Won First Choice ' + CONVERT(VARCHAR(15), GETDATE(), 101)
                                                WHERE FirewoodLottery_id = @ApplicantID
												
												
												SELECT @TownCount = @PermitsCount - 1 

                                                UPDATE survey.FirewoodTowns
                                                SET PermitsCountdown = @TownCount
                                                WHERE Town = @FirstChoice

                                END
                                ELSE
                                BEGIN
                                                -- first choice is full so figure out if second choice is full
                                                SELECT @PermitsCount = [PermitsCountdown]
												FROM survey.FirewoodTowns
												WHERE [Town] = @SecondChoice

                                                IF @PermitsCount > 0
                                                BEGIN
                                                  SELECT @TownCount = @PermitsCount - 1

                                                  UPDATE survey.FirewoodTowns
                                                  SET PermitsCountdown = @TownCount
                                                  WHERE Town = @SecondChoice

                                                  UPDATE survey.FirewoodLottery
                                                  SET assigned = @SecondChoice
                                                     ,notes = 'Won Second Choice ' + CONVERT(VARCHAR(15), GETDATE(), 101)
                                                  WHERE FirewoodLottery_id = @ApplicantID
                                                END
                                                ELSE
                                                BEGIN
                                                -- report that both choices are full
                                                   UPDATE survey.FirewoodLottery
                                                   SET assigned = 'Nothing'
                                                      ,notes = 'Both Choices Full ' + CONVERT(VARCHAR(15), GETDATE(), 101)
                                                   WHERE FirewoodLottery_id = @ApplicantID
                                                END

                                                -- Reset looping variables.           
                                                SELECT @iNextRowId = NULL

                                                -- get the next iRowId
                                                SELECT @iNextRowId = MIN(iRowId)
                                                FROM #RandomizedApplicants
                                                WHERE iRowId > @iCurrentRowId

                                                -- did we get a valid next row id?
                                                IF ISNULL(@iNextRowId, 0) = 0
                                                BEGIN
                                                  BREAK
                                                END

                                                -- get the next row.
                                                SELECT @iCurrentRowId = iRowId
                                                      ,@ApplicantID = FirewoodLottery_id
                                                      ,@FirstChoice = choice1
                                                      ,@SecondChoice = choice2
                                                FROM #RandomizedApplicants
                                                WHERE iRowId = @iNextRowId
                                END
                END

                RETURN
END
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

Post #1428562

Lynn Pettis

Posted Friday, March 08, 2013 7:15 AM

SSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:00 PM
Points: 21,588, Visits: 27,373

Welcome to SSC. As it appears you are new here there are some things you need to do to helps help you and for you to get better answers to your problem.

First, start by reading the first article I reference below in my signature block regarding asking for help. The instructions in that article will help with the information you need to post and how to post it.

We really do need the DDL (CREATE TABLE) statement for the table(s) involved, sample data (as a series of INSERT INTO statements) for the table(s) involved, and the expected results based on the sample data. Please note, we aren't asking for actual, confidential data as sample data. We just want data that is representative of your problem domain and the problem you are trying to solve.

You do this and you will get good answers in return plus the code provided will be tested against the data you provided.

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1428566

Siobhan Perricone

Posted Friday, March 08, 2013 7:22 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 11:58 AM
Points: 14, Visits: 20

Didn't realize which forum I was on or that I was so new. Thanks for the tips, sorry for the inadequate post.

Post #1428571

Lynn Pettis

Posted Saturday, March 09, 2013 5:45 AM

SSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:00 PM
Points: 21,588, Visits: 27,373

Don't apologize for your post. I've looked at the code you posted, it's just that there isn't enough there to really work with. Since we don't know the table structures or the data you are working with we can't setup a test environment on our systems to properly work the problem.

As volunteers we rely on you to provide us the information we need to be able to help you. The more you can do to helps, the better help we can provide you. In addition, you get the benefit of tested code as well, not just something thrown together that might possibly be a solution to your problem.

Cool

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1428873

GilaMonster

Posted Saturday, March 09, 2013 5:51 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 2:45 PM
Points: 37,635, Visits: 29,885

Just worth noting, a while loop is just as much a cursor as an explicitly defined cursor. It just doesn't have the keyword. It's still a loop, it's still processing a row at a time, it's still slow.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1428874

Jeff Moden

Posted Saturday, March 09, 2013 7:32 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 6:33 PM
Points: 32,889, Visits: 26,757

Siobhan Perricone (3/8/2013)

I got asked to not use a cursor for a loop that I'm working on so I grabbed the code here:
http://www.sql-server-performance.com/2004/operations-no-cursors/

To emphasize what Gail has stated, they should outlaw articles like the one you provided the link for. Probably most important is the fact that the author has produced absolutely NO performance stats on the two methods and, like many of us had in the early days until we tested, has bought into the myth that Temp Tables or Table Variables and While loops are more efficient than Cursors. A well written "firehose" cursor is just as effecient and easier to code than such alternatives. It's usually a total waste of time to convert Cursors to alternatives. You won't actually believe it until you prove it to yourself with code.

Don't buy into the "Cursor Replacement Myth" of replacing cursors with While Loops and Temp Tables. It usually makes no difference. If you feel you must, just convert the cursor to a "firehose" cursor and call it a day.

What you should buy into, however, is that there's usually a very high performance method to avoid RBAR (see my sig line below for definition) altogether. Even multiple set-based passes on a Temp Table will blow any While loop away whether in a Cursor or not.

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1428884

SQLRNNR

Posted Saturday, March 09, 2013 8:24 AM

SSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:46 PM
Points: 18,732, Visits: 12,329

Jeff Moden (3/9/2013)

Siobhan Perricone (3/8/2013)

I got asked to not use a cursor for a loop that I'm working on so I grabbed the code here:
http://www.sql-server-performance.com/2004/operations-no-cursors/

I will third that opinion. This is an argument that I have heard time and again that a while loop is more efficient. Yuck.
Here is a good article on comparing/ converting cursors to while loops and why the conversion just isn't what is advertised by that sql-server-performance article referenced above:
http://sqlblog.com/blogs/aaron_bertrand/archive/2012/01/26/the-fallacy-that-a-while-loop-isn-t-a-cursor.aspx

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

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 #1428896

Jeff Moden

Posted Saturday, March 09, 2013 10:58 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 6:33 PM
Points: 32,889, Visits: 26,757

Getting back to the problem at hand, I did a read of the code and I don't readily see the problem. I'll take another look after I get some shuteye.

--Jeff Moden

Post #1428956

Siobhan Perricone

Posted Monday, March 11, 2013 7:39 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 11:58 AM
Points: 14, Visits: 20

Here's my attempt at providing enough information so you can help. :)

I wasn't sure how to give you all the necessary code, so I'm dropping it all in at the end of my description here. I also, after I'd posted last week, gone back and tried redoing this as a cursor in the hope that it was simply my unfamiliarity that might be at issue, and I'm having the same problems even with the cursor. Since I've already posted my first attempt without the cursor above, I'll only add the second attempt with the cursor after all the set up code at the bottom.

Every year there's a lottery held to award the right to harvest on state-owned firewood lots to citizens. There is a web application they apply through (I did not write it, I didn't design any of this, I inherited it when a coworker suddenly passed away last year). The old code was using cold fusion and Microsoft Access with just two tables on an SQL Backend. I'm trying to update this so that I don't have to go through the laborious Access process my predecessor had set up without having to rewrite everything he did (we're not replacing the cold fusion, yet for instance, it works well enough for now). So the task at hand is to set up the code that will award the lots appropriately.

There are a specific number of lots awarded in different towns. The towns and the numbers of lots available changes every year. There are also specific numbers of alternates awarded afterwards. I was intending to simply create a second process the mirrored the first to do the alternate awards. I'm hoping that the comments in the code will make it clear what the process is, but I'm concerned that I might be bogging this down, so if you need me to outline the lottery procedure, I can do that, too, just let me know.

-- creating the two tables
CREATE TABLE [survey].[FirewoodLottery](
	[FirewoodLottery_id] [int] IDENTITY(1,1) NOT NULL,
	[Firstname] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[MiddleInitial] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Lastname] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Suffix] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[address] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[city] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[state] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[zip] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[phone] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[email] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[postdate] [datetime] NULL,
	[ip] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[choice1] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[choice2] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[assigned] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[status] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[District] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[sortindex] [int] NULL,
	[choice] [int] NULL,
	[Notes] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[possible_dup] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]


CREATE TABLE [survey].[FirewoodTowns](
	[FirewoodTowns_id] [int] IDENTITY(1,1) NOT NULL,
	[Town] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[District] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Permits] [int] NULL,
	[Forest] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[alternates] [int] NULL,
	[PermitsCountdown] [int] NULL CONSTRAINT [DF_FirewoodTowns_PermitsCountdown]  DEFAULT ((0)),
	[AlternatesCountdown] [int] NULL CONSTRAINT [DF_FirewoodTowns_AlternatesCountdown]  DEFAULT ((0))
) ON [PRIMARY]

-- creating the stored procedures referenced

CREATE PROCEDURE [survey].[sp_GetFirewoodLotteryTowns] 
AS
BEGIN

Select Town, Permits, alternates, PermitsCountdown, AlternatesCountdown
from survey.FirewoodTowns
END

CREATE PROCEDURE [survey].[sp_GetRandomizedApplicants] 
AS
BEGIN

select TOP 800 FirewoodLottery_id
               ,choice1
               ,choice2
FROM survey.FirewoodLottery
ORDER BY NEWID()

END

CREATE PROCEDURE [survey].[sp_ResetPermitsCountdown] 
AS
BEGIN

Declare @LotteryTownName as varchar(40)
Declare @NumberOfPermits as int
Declare @NumberOfAlternates as int
Declare @CountdownOfPermits as int
Declare @CountdownOfAlternates as int
Declare @LoopCounter as int
Declare @RecordCount as int

set nocount on
create table #LotteryTowns (
 Town varchar(40),
 Permits int,
 Alternates int,
 PermitsCountdown int,
 AlternatesCountdown int
 )
INSERT INTO #LotteryTowns exec survey.sp_GetFirewoodLotteryTowns
  select @RecordCount = count(town) from #LotteryTowns

declare cur cursor 
for
  select Town, Permits, Alternates, PermitsCountdown, AlternatesCountdown
    from #LotteryTowns

  open cur
  set @LoopCounter = 0
/* Start loop here */
  while @LoopCounter < @RecordCount
    begin 
      fetch next from cur into @LotteryTownName, @NumberOfPermits, @NumberOfAlternates, @CountdownOfPermits, @CountdownOfAlternates;
        if @CountdownOfPermits <> @NumberOfPermits
			update [survey].[FirewoodTowns]
				set PermitsCountdown = @NumberOfPermits
			where Town = @LotteryTownName

		SET @LoopCounter = @LoopCounter + 1;
/* End loop here */
    end
  close cur
  deallocate cur 
drop table #LotteryTowns

END


-- set identity insert on for lottery

SET IDENTITY_INSERT survey.FirewoodLottery ON

-- entering sample data into lottery table
 INSERT INTO [survey].[FirewoodLottery]
       (ID, DateValue, Value, YearValue, Monthvalue)
			[Firstname]
           ,[MiddleInitial]
           ,[Lastname]
           ,[Suffix]
           ,[address]
           ,[city]
           ,[state]
           ,[zip]
           ,[phone]
           ,[email]
           ,[postdate]
           ,[ip]
           ,[choice1]
           ,[choice2]
           ,[assigned]
           ,[status]
           ,[District]
           ,[sortindex]
           ,[choice]
           ,[Notes]
           ,[possible_dup]
select 1,'Wombat',,'Champine',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Shrewsbury','Plymouth',,'Dupe',,,,,union all
select 2,'Wombat',,'Beaudry',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Shrewsbury','Plymouth',,,,,,,union all
select 3,'Wombat',,'Couture',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union all
select 4,'Wombat',,'VanHorn',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','North Duxbury','North Duxbury',,,,,,,union all
select 5,'Wombat',,'Roach',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','North Duxbury','Johnson',,,,,,,union all
select 6,'Wombat',,'Relation',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union all
select 7,'Wombat',,'LaRose',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union all
select 8,'Wombat',,'Deuso',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','North Duxbury',,,,,,,union all
select 9,'Wombat',,'Valley',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union all
select 10,'Wombat',,'Beaudoin',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union all
select 11,'Wombat',,'VanHorn',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','North Duxbury','North Duxbury',,,,,,,union all
select 12,'Wombat',,'Robinson',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union all
select 13,'Wombat',,'Green',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Shrewsbury','Plymouth',,,,,,,union all
select 14,'Wombat',,'Wheeler',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Shrewsbury','Shrewsbury',,,,,,,union all
select 15,'Wombat',,'Trombley',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Orange',,,,,,,union all
select 16,'Wombat',,'Trombley',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Orange',,,,,,,union all
select 17,'Wombat',,'Compo',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Orange',,,,,,,union all
select 18,'Wombat',,'Kipp',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union all
select 19,'Wombat',,'Auger',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union all
select 20,'Wombat',,'Auger',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union all
select 21,'Wombat',,'Elliott','Jr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Westmore',,,,,,,union all
select 22,'Wombat',,'Morse',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Johnson','North Duxbury',,,,,,,union all
select 23,'Wombat',,'Morse',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Johnson','North Duxbury',,,,,,,union all
select 24,'Wombat',,'Beauchemin',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Roxbury','Orange',,,,,,,union all
select 25,'Wombat',,'Curtis',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Roxbury','North Duxbury',,,,,,,union all
select 26,'Wombat',,'charron','mr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Groton',,,,,,,union all
select 27,'Wombat',,'Barnett','IV','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Orange',,,,,,,union all
select 28,'Wombat',,'Duke',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Orange',,,,,,,union all
select 29,'Wombat',,'Gerrish',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union all
select 30,'Wombat',,'charron',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Groton',,,,,,,union all
select 31,'Wombat',,'Smith',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Orange',,,,,,,union all
select 32,'Wombat',,'Gonyaw','Mrs.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Orange',,,,,,,union all
select 33,'Wombat',,'Gonyaw','Mr.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Orange',,,,,,,union all
select 34,'Wombat',,'charron',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Groton',,,,,,,union all
select 35,'Wombat',,'charron',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Groton',,,,,,,union all
select 36,'Wombat',,'West',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Orange',,,,,,,union all
select 37,'Wombat',,'Gerrish','Jr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union all
select 38,'Wombat',,'Clark','G','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union all
select 39,'Wombat',,'Palmer','Mr.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Shrewsbury','Plymouth',,,,,,,union all
select 40,'Wombat',,'Anderson','Mr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union all
select 41,'Wombat',,'Anderson','Mr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union all
select 42,'Wombat',,'Moser',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Johnson','Johnson',,,,,,,union all
select 43,'Wombat',,'beyor',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Orange',,'Dupe',,,,,union all
select 44,'Wombat',,'richart','mr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','North Duxbury','Roxbury',,,,,,,union all
select 45,'Wombat',,'Tighe',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union all
select 46,'Wombat',,'Abare',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Groton',,,,,,,union all
select 47,'Wombat',,'Willette',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Roxbury','Orange',,,,,,,union all
select 48,'Wombat',,'Hinman',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union all
select 49,'Wombat',,'Barnett','111','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Groton',,,,,,,union all
select 50,'Wombat',,'Ware',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union all
select 51,'Wombat',,'Ware',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union all
select 52,'Wombat',,'Hackett',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union all
select 53,'Wombat',,'Drinkwater',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union all
select 54,'Wombat',,'Hale',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union all
select 55,'Wombat',,'John',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union all
select 56,'Wombat',,'John',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union all
select 57,'Wombat',,'John',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union all
select 58,'Wombat',,'alexander',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Shrewsbury','Shrewsbury',,,,,,,union all
select 59,'Wombat',,'rogers',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Shrewsbury','Shrewsbury',,,,,,,union all
select 60,'Wombat',,'Webster','Jr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Johnson',,,,,,,union all
select 61,'Wombat',,'Celley',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Orange',,,,,,,union all
select 62,'Wombat',,'Gerrish','Sr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union all
select 63,'Wombat',,'Wilson',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Johnson','Plymouth',,,,,,,union all
select 64,'Wombat',,'Tuthill',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Johnson','Johnson',,,,,,,union all
select 65,'Wombat',,'hall',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Shrewsbury','Plymouth',,,,,,,union all
select 66,'Wombat',,'Habel',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Roxbury','Roxbury',,,,,,,union all
select 67,'Wombat',,'Barnett','Jean','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Groton',,,,,,,union all
select 68,'Wombat',,'Hudson',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Orange',,,,,,,union all
select 69,'Wombat',,'Carleton','L','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union all
select 70,'Wombat',,'Paige',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Plymouth','Shrewsbury',,,,,,,union all
select 71,'Wombat',,'Brown',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union all
select 72,'Wombat',,'Rhodes',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union all
select 73,'Wombat',,'Wyckoff',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','North Duxbury','Johnson',,,,,,,union all
select 74,'Wombat',,'Rhodes',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union all
select 75,'Wombat',,'LaCasse',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union all
select 76,'Wombat',,'Chase',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Johnson','Groton',,,,,,,union all
select 77,'Wombat',,'keene',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Johnson',,,,,,,union all
select 78,'Wombat',,'Huff',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Orange',,,,,,,union all
select 79,'Wombat',,'Irwin',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Johnson','North Duxbury',,,,,,,union all
select 80,'Wombat',,'Conway',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Groton',,,,,,,union all
select 81,'Wombat',,'page','d','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','North Duxbury','North Duxbury',,,,,,,union all
select 82,'Wombat',,'Webster',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union all
select 83,'Wombat',,'Garrow',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Shrewsbury','Plymouth',,,,,,,union all
select 84,'Wombat',,'Roy','Mr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Johnson','North Duxbury',,,,,,,union all
select 85,'Wombat',,'Gifford','Mr.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Westmore',,,,,,,union all
select 86,'Wombat',,'LeBlanc',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','North Duxbury','Shrewsbury',,,,,,,union all
select 87,'Wombat',,'Quintin','Jr.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Groton',,,,,,,union all
select 88,'Wombat',,'Anderson',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union all
select 89,'Wombat',,'Couch',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Plymouth','Shrewsbury',,,,,,,union all
select 90,'Wombat',,'Gove',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Groton',,,,,,,union all
select 91,'Wombat',,'Grass',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Shrewsbury','Plymouth',,,,,,,union all
select 92,'Wombat',,'Domina',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Johnson','Johnson',,,,,,,union all
select 93,'Wombat',,'Barney','Jr.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union all
select 94,'Wombat',,'Royer',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union all
select 95,'Wombat',,'Wheeler',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','North Duxbury','Westmore',,,,,,,union all
select 96,'Wombat',,'Lapan',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Orange',,,,,,,union all
select 97,'Wombat',,'Martell','Mr.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','North Duxbury','Roxbury',,,,,,,union all
select 98,'Wombat',,'Deutschbein',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Plymouth','Shrewsbury',,,,,,,union all
select 99,'Wombat',,'maston',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Plymouth','Shrewsbury',,,,,,,union all
select 100,'Wombat',,'Teer',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Shrewsbury','Shrewsbury',,'Dupe',,,,,union all
select 101,'Wombat',,'Teer',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Shrewsbury','Shrewsbury',,'Dupe',,,,,union all
select 102,'Wombat',,'Inman',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Plymouth','Shrewsbury',,,,,,,union all
select 103,'Wombat',,'Teer',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Shrewsbury','Plymouth',,,,,,,union all
select 104,'Wombat',,'Teer',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Shrewsbury','Plymouth',,'Dupe',,,,,union all
select 105,'Wombat',,'Putney',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union all
select 106,'Wombat',,'Russo',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','North Duxbury','Groton',,,,,,,union all
select 107,'Wombat',,'Davis',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union all
select 108,'Wombat',,'Smith',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Roxbury','Orange',,,,,,,union all
select 109,'Wombat',,'Bancroft',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Orange',,,,,,,union all
select 110,'Wombat',,'Nolan','none','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union all
select 111,'Wombat',,'LeBlanc','Mr.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Orange',,,,,,,union all
select 112,'Wombat',,'LeBlanc','Mr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Orange',,,,,,,union all
select 113,'Wombat',,'follert','k','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Johnson','Johnson',,,,,,,union all
select 114,'Wombat',,'Demar',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union all
select 115,'Wombat',,'beauchemin',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Roxbury','Orange',,,,,,,union all
select 116,'Wombat',,'Doyon',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Orange',,,,,,,union all
select 117,'Wombat',,'Beyor','Mrs.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Orange',,,,,,,union all
select 118,'Wombat',,'Bearce','Mr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Westmore',,,,,,,union all
select 119,'Wombat',,'Millard','Mr.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Plymouth','Shrewsbury',,,,,,,union all
select 120,'Wombat',,'Fitzpatrick',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','North Duxbury',,,,,,,,
select 
-- set identity insert on for towns
SET IDENTITY_INSERT survey.FirewoodTowns ON

-- entering sample data into lottery table
INSERT INTO [survey].[FirewoodTowns]
           ([Town]
           ,[District]
           ,[Permits]
           ,[Forest]
           ,[alternates]
           ,[PermitsCountdown]
           ,[AlternatesCountdown)
select 14,'Plymouth','1',15,'Coolidge SF',5,15,0 union all
select 15,'Shrewsbury','2',8,'Coolidge SF',5,8,0 union all
select 16,'North Duxbury','3',10,'Camels Hump SF',10,10,0 union all
select 17,'Orange','4',10,'Groton SF',0,0,0 union all
select 18,'Roxbury','4',3,'Roxbury SF',0,3,0 union all
select 19,'Johnson','4',5,'Mt Mansfield SF',0,5,0 union all
select 20,'Groton','5',15,'Groton SF',10,0,0 union all
select 21,'Westmore','5',15,'Willoughby SF',10,15,0

Here's with the cursor:

CREATE PROCEDURE [survey].[sp_AwardFirewoodLots]
AS
BEGIN

Declare @ApplicantID as INT
Declare @TownCount as INT
Declare @PermitsCount as INT
Declare @FirstChoice as NCHAR(50)
Declare @SecondChoice as NCHAR(50)
Declare @LoopCounter as int
Declare @RecordCount as int

set nocount on

exec [survey].[sp_ResetPermitsCountdown]


create table #RandomizedApplicants (
FirewoodLottery_id int,
Choice1 varchar(40),
Choice2 varchar(40)
)

INSERT INTO #RandomizedApplicants exec survey.[sp_GetRandomizedApplicants]

select @RecordCount = count(FirewoodLottery_id) from #RandomizedApplicants


declare cur cursor

for
  select FirewoodLottery_id, choice1, choice2
    from #RandomizedApplicants
  open cur
  set @LoopCounter = 0

/* Start loop here */

  while @LoopCounter < @RecordCount
    begin
      fetch next from cur into @ApplicantID, @FirstChoice, @SecondChoice;

                      SELECT @PermitsCount = [PermitsCountdown]
                      FROM survey.FirewoodTowns
                      WHERE [Town] = @FirstChoice

                         IF @PermitsCount <> 0
                         BEGIN
			               UPDATE survey.FirewoodLottery
		        	       SET assigned = @FirstChoice
                               ,notes = 'Won First Choice ' + CONVERT(VARCHAR(15), GETDATE(), 101)
                           WHERE FirewoodLottery_id = @ApplicantID
												
			               SELECT @TownCount = @PermitsCount - 1 

                            UPDATE survey.FirewoodTowns
                            SET PermitsCountdown = @TownCount
                            WHERE Town = @FirstChoice

                         END
                      ELSE
                         BEGIN
                         -- first choice is full so figure out if second choice is full
                           SELECT @PermitsCount = [PermitsCountdown]
			               FROM survey.FirewoodTowns
			               WHERE [Town] = @SecondChoice

                            IF @PermitsCount <> 0
                             BEGIN
                               SELECT @TownCount = @PermitsCount - 1

                               UPDATE survey.FirewoodTowns
                               SET PermitsCountdown = @TownCount
                               WHERE Town = @SecondChoice

                               UPDATE survey.FirewoodLottery
                               SET assigned = @SecondChoice
                                  ,notes = 'Won Second Choice ' + CONVERT(VARCHAR(15), GETDATE(), 101)
                               WHERE FirewoodLottery_id = @ApplicantID
                             END
                           ELSE
                             BEGIN
                             -- report that both choices are full
                             UPDATE survey.FirewoodLottery
                             SET assigned = 'Nothing'
                             ,notes = 'Both Choices Full ' + CONVERT(VARCHAR(15), GETDATE(), 101)
                             WHERE FirewoodLottery_id = @ApplicantID
                             END
            SET @LoopCounter = @LoopCounter + 1;

/* End loop here */
    end
  close cur
  deallocate cur
drop table #RandomizedApplicants

 end

END

Post #1429263

Sean Lange

Posted Monday, March 11, 2013 8:34 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 8:46 AM
Points: 8,547, Visits: 8,204

Nice attempt at posting ddl and sample data. I would recommend you try running your code prior to posting. There are literally hundreds of issues with what you posted. Some are reasonably simple to fix (adding a GO between procs) but others are just too much to deal with. Your insert into FirewoodLottery has more things wrong than it has right. If you can clean this up so it is usable we can have a crack at this.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1429303

« Prev Topic | Next Topic »

39 posts, Page 1 of 4

1 2 3 4 »»»

Permissions