June 17, 2026 at 5:39 pm
I have an issue where I have a Bill of Material list of items where some of the item numbers are blank. I need to give them sequential numbers from a beginning number like 9000000. then the next blank would be 9000001 and so on. I thought I could create a table and store the number and increment it using a function because I need the function to return other item numbers based on the item number. But when the item number is blank, I am screwed because a function can't update a table. and a stored procedure can't return a value. Any ideas would be appreciated.
Thanks
June 17, 2026 at 5:46 pm
BTW, it is starting from a view not a SP
June 17, 2026 at 5:54 pm
without some tables it's impossible to diagnose or help.
Sounds like a job for ROW_NUMBER() but it's hard to say for sure.
June 17, 2026 at 6:46 pm
Sounds like you could use a SEQUENCE. For example:
CREATE SEQUENCE dbo.bom_sequence AS int START WITH 9000000 INCREMENT BY 1 NO CYCLE ;
SELECT CASE WHEN item_number = '' THEN NEXT VALUE FOR dbo.bom_sequence ELSE item_number END, ...
FROM ...
WHERE ...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply