How to get and use the value returned by SP to a INSERT INTO... SELECT... statement

  • 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

  • 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;

  • 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)

  • 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.

  • Thong Lam - Tuesday, June 5, 2018 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;

    Thank you.ü I'll try your suggestion.

  • Djrivers2002 - Tuesday, June 5, 2018 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.

    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)

  • 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. 🙂

  • 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".

  • 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