June 4, 2018 at 8:30 pm
Hi,
I am just new in SQL language and still studying it. I'm having hard time looking for answer on how can I use SP and insert value into a table. So I have this SP:
CREATE PROCEDURE TestID
AS
SET NOCOUNT ON;
BEGIN
DECLARE @NewID VArchar(30),
@GenID INT,
@BrgyCode varchar(5) = '23548'
SET @GenID = (SELECT TOP (1) NextID FROM dbo.RandomIDs WHERE IsUsed = 0 ORDER BY RowNumber)
SET @NewID = @BrgyCode + '-' + CAST(@GenID AS VARCHAR (30))
UPDATE dbo.RandomIDs
SET dbo.RandomIDs.IsUsed = 1
WHERE dbo.RandomIDs.NextID = @GenID
SELECT @NewID OUTPUT
END;
and what I'm trying to do is this:
INSERT INTO dbo.Residents(
[ResidentID],
NewResidentID,
[ResLogdate],
...
SELECT
[ResidentID],
EXEC TestID ,
[ResLogdate],
....
FROM dbo.Resident;
There is a table named dbo.RandomIDs containing random 6 digit non repeating numbers where I'm pulling out the value via the SP and updating the IsUsed column of the table to 1.
But I can't get it to work 🙁 I've been searching the net for hours now but I'm not getting the information that I need and that the reason for my writing. I hope someone could help me with this.
Thanks,
Darren
June 5, 2018 at 2:14 am
Hi, you can try this script:
DECLARE @BrgyCode VARCHAR(5) = '23548';
SELECT
Ran.NextID,
Res.ResidentID,
@BrgyCode + '-' + CAST(Ran.NextID AS VARCHAR (30)) AS NewResidentID,
Res.ResLogDate
INTO #TempResident
FROM
(SELECT ROW_NUMBER() OVER(ORDER BY ResidentID) RowID, * FROM dbo.Resident) Res
JOIN (SELECT ROW_NUMBER() OVER(ORDER BY RowNumber) RowID, * FROM dbo.RandomIDs WHERE IsUsed = 0) Ran
ON Ran.RowID = Res.RowID;
UPDATE RandomIDs SET IsUsed = 1 WHERE NextID IN (SELECT NextID FROM #TempResident);
INSERT INTO Resident(ResidentID, NewResidentID, ResLogDate)
SELECT ResidentID, NewResidentID, ResLogDate FROM #TempResident;
DROP TABLE #TempResident;
June 5, 2018 at 6:48 am
There's a much better way to get numbers that don't get repeated. And you don't even need a table to keep track of them. It's called a sequence object. You'll want to search for SQL Server SEQUENCE. Once you create one, you get the next number in it by using FETCH NEXT FROM SeqObjName INTO @NumericVariableName.
You need to declare the integer variable to hold that value, and it can be a bigint if you wish. Just be sure to set up the sequence as a bigint as well. That solves a lot of the kinds of problems associated with guaranteeing uniqueness. You can even set up the sequence to start at any given number. The one thing you can't do is guarantee that you'll never have any gaps. If you fetch a new number before a user decides to commit to a specific action that requires an insert, then that number may get wasted, so you can easily end up with gaps. However, gaps are not really something you need to avoid.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 5, 2018 at 7:35 am
Does the SEQUENCE generates random numbers? Because what I need is a random number as a requirement. It should not be sequential thus I created/generated random non repeating numbers in advance so as not to exhaust the system in creating a new one everytime it will asked to by the user.
June 5, 2018 at 7:36 am
Thong Lam - Tuesday, June 5, 2018 2:14 AMHi, you can try this script:
DECLARE @BrgyCode VARCHAR(5) = '23548';
SELECT
Ran.NextID,
Res.ResidentID,
@BrgyCode + '-' + CAST(Ran.NextID AS VARCHAR (30)) AS NewResidentID,
Res.ResLogDate
INTO #TempResident
FROM
(SELECT ROW_NUMBER() OVER(ORDER BY ResidentID) RowID, * FROM dbo.Resident) Res
JOIN (SELECT ROW_NUMBER() OVER(ORDER BY RowNumber) RowID, * FROM dbo.RandomIDs WHERE IsUsed = 0) Ran
ON Ran.RowID = Res.RowID;UPDATE RandomIDs SET IsUsed = 1 WHERE NextID IN (SELECT NextID FROM #TempResident);
INSERT INTO Resident(ResidentID, NewResidentID, ResLogDate)
SELECT ResidentID, NewResidentID, ResLogDate FROM #TempResident;DROP TABLE #TempResident;
Thank you.ü I'll try your suggestion.
June 5, 2018 at 8:16 am
Djrivers2002 - Tuesday, June 5, 2018 7:35 AMDoes the SEQUENCE generates random numbers? Because what I need is a random number as a requirement. It should not be sequential thus I created/generated random non repeating numbers in advance so as not to exhaust the system in creating a new one everytime it will asked to by the user.
Define the meaning of RANDOM .... Stop and think about how your application works. Also remember that you can use bigint as the data type, which is a rather significantly larger universe of numbers. Integers can only go from -2,147,483,648 to 2,147,483,647, whereas a bigint can go from -9,223,372,036,854,775,808 through 9,223,372,036,854,775,807. That's a range of 18.4+ QUADRILLION.... If you could grab 1,000 values per second 24 hours a day 7 days a week for the next 292,277,264 years, you would still be at least 6 months away from starting to use negative numbers. Stop worrying about running out of numbers...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 5, 2018 at 8:30 am
Random meaning just not in sequence. The format needed is #####-###### where the first 5 numbers is the community code and the next 6 random nonrepeating number is for the resident identification. One community will only be composed of a maximum of 150,000 people so I'm fine INT. 🙂
June 5, 2018 at 2:05 pm
Below is a re-coded version of the stored proc. It does the UPDATE of IsUsed inline, to reduce (eliminate?) chances of two near-same-time execs of it getting the same NextID value, and has some other clean up.
Unfortunately, you can't run the proc inline, and you can't convert the code to make it a function (which automatically could be used inline).
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE dbo.TestID
@NewID varchar(30) OUTPUT
AS
SET NOCOUNT ON;
DECLARE @NextIDs table ( NextID int );
UPDATE RI
SET RI.IsUsed = 1
OUTPUT INSERTED.NextID into @NextIDs
FROM (
SELECT TOP (1) *
FROM dbo.RandomIDs
WHERE IsUsed = 0
ORDER BY RowNumber
) AS RI
SET @NewID = '23548-' + (SELECT TOP (1) CAST(NextID AS varchar(30)) FROM @NextIDs)
GO
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".
June 5, 2018 at 3:17 pm
Thank you ScottPletcher. 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply