December 8, 2011 at 2:01 pm
Old guy here, new to database coding....NEED HELP PLEASE!!
I have a stored procedure that works great for adding units to my unit table but I would now like it to add a sequential number to the subaddress field for each record that gets added to the table as well, while its looping through and adding units.....here is my stored procedure...
ALTER PROCEDURE [dbo].[sp_ConvertAddUnits]
@unitnum INT, @bldg_id INT, @phase_id INT
AS
DECLARE @index INT;
SET @index = 0
WHILE @index < @unitnum -1
BEGIN
select 'loop counter = ', @index
INSERT INTO dbo.Units (PhaseID, BuildingID, UnitName, FileNumber, SubAddress, Eligible, EligibleReason, Priority, TownPriority, BidAmount, ConstructionCost, Comments, AccessCode)
VALUES (@phase_id,@bldg_id,'','','','','','','','','','','');
END
This works great, adds rows as desired. Any ideas on how I could add a iterative number to the subaddress field as it runs, as in each iteration through add a number like 1 for first record added, 2 for second record added, etc.
Any and all help will be most appreciated!!
Thanks!
December 8, 2011 at 2:05 pm
Welcome RW.
Why not just do this?
INSERT INTO dbo.Units (PhaseID, BuildingID, UnitName, FileNumber, SubAddress, Eligible, EligibleReason, Priority, TownPriority, BidAmount, ConstructionCost, Comments, AccessCode)
VALUES (@phase_id,@bldg_id,'','',@index,'','','','','','','','');
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 9, 2011 at 9:52 am
Craig,
Thanks! I really appreciate the help.
That works for the most part. It does number the units, except it skips the first one. Basically starting at 1 with second record added.
Here is the modified sp...
ALTER PROCEDURE [dbo].[sp_ConvertAddUnits]
@unitnum INT, @bldg_id INT, @phase_id INT
AS
DECLARE @index INT;
SET @index = 0
WHILE @index < @unitnum - 1
BEGIN
select 'loop counter = ', @index
INSERT INTO dbo.Units (PhaseID, BuildingID, UnitName, FileNumber, SubAddress, Eligible, EligibleReason, Priority, TownPriority, BidAmount, ConstructionCost, Comments, AccessCode)
VALUES (@phase_id,@bldg_id,'','',@index,'','','','','','','','');
END
I also tried setting @index to 1 instead of 0, still skips the first record.
Any ideas?
Thanks again for your help!!!
December 9, 2011 at 12:43 pm
rwsmith 38519 (12/9/2011)
Craig,Thanks! I really appreciate the help.
That works for the most part. It does number the units, except it skips the first one. Basically starting at 1 with second record added.
That's... really wierd.
Try this and tell me how it works for you?
ALTER PROCEDURE [dbo].[sp_ConvertAddUnits]
@unitnum INT, @bldg_id INT, @phase_id INT
AS
DECLARE @index INT;
SET @index = 1
WHILE @index < @unitnum - 1
BEGIN
select 'loop counter = ', @index
INSERT INTO dbo.Units (PhaseID, BuildingID, UnitName, FileNumber, SubAddress, Eligible, EligibleReason, Priority, TownPriority, BidAmount, ConstructionCost, Comments, AccessCode)
VALUES (@phase_id,@bldg_id,'','',@index,'','','','','','','','');
SET @index = @index + 1
END
If that doesn't work, can you run it for @unitnum = 5 and show me the results in the window? Output as text and then copy/paste here in the code="plain" tags.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 9, 2011 at 1:40 pm
Craig, by the way, Evil Kraig is a cool pen name....
I solved the issue, I was looking at how the index is being incremented and thought why not another variable so I added one and boom, worked like a charm.....This is what I ended up with....
ALTER PROCEDURE [dbo].[sp_ConvertAddUnits]
@unitnum INT, @bldg_id INT, @phase_id INT
AS
DECLARE @index INT;
DECLARE @Number INT;
SET @Number = 1
SET @index = 0
WHILE @index < @unitnum
BEGIN
select 'loop counter = ', @index
INSERT INTO dbo.Units (PhaseID, BuildingID, UnitName, FileNumber, SubAddress, Eligible, EligibleReason, Priority, TownPriority, BidAmount, ConstructionCost, Comments, AccessCode)
VALUES (@phase_id,@bldg_id,'','',@Number,'','','','','','','','');
SET @Number = @Number + 1
END
Just wanted to thank you again for all of your help, never would have found it without you!!
Thanks !
RW
December 9, 2011 at 2:28 pm
rwsmith 38519 (12/9/2011)
Craig, by the way, Evil Kraig is a cool pen name....
Heheh, thanks. It really started with a mis-spelling by someone ubobservant, but I couldn't resist the concept of being my own Evil Twin. 😀
I solved the issue, I was looking at how the index is being incremented and thought why not another variable so I added one and boom, worked like a charm.....This is what I ended up with....
Seems a bit of overkill, but hey, if it works and you understand it, it's a perfect solution. 🙂
Just wanted to thank you again for all of your help, never would have found it without you!!
Thanks !
RW
My pleasure. Out of curiousity, how many units do you expect to be adding at a time, and how often will something like this occur? Just so you know, While and Cursor loops are really a last resort in T-SQL, we usually try to do everything set based. There's some Tally Table solutions and the like that would work well for this, but I'm not sure how in-depth you want to get in your database solutions, and if they'd be overkill for this or not. The more often this occurs, and the more rows you're adding this way, the more you'll want the overkill.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply