Help with SQL Coding

  • Hi Guys,

    I am writing a stored procedure. My objective is to put a random number in a column of table, where the fields are null. So my column is App_ID. But the catch is, the App_ID column must have unique numbers and since I am generating numbers randomly there maybe instances when the same random number is generated. Therefore to take care of that I was using IF statement. So part of my code is below but I get an error "Invalid column name 'App_ID' ".

    DECLARE @temp int

    SET @temp = ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) --This code generates a random number

    IF @temp = App_ID --This is the part giving error.

    BEGIN

    UPDATE Event_Person_Info

    SET App_ID = @temp

    Where (NOT(App_ID IS NULL))

    END

    END

    I know why I get the error, its because App_ID is a column and @temp is variable. Since I want to check if the value stored in @temp is present within the column App_ID, if not present then I will UPDATE. Can anyone please help me how can I do this check within the column App_ID. Would be great if you could modify the given code.

    Thanks in Advance!

  • With the given information, it would be difficult to provide tested code , but check if following works..

    --Change Below code

    --IF @temp = App_ID --This is the part giving error.

    --to

    IF exists (select app_id from Event_Person_Info where app_id=@temp)

    -----------------------------------------------------------------------
    For better assistance in answering your questions[/url]
    Perforamance Issues[/url]
    Cross Tabs and Pivots[/url]
    Cross Apply[/url]
    The Numbers or Tally Table- Jeff Moden[/url]

  • Your question has made my spidey-sense tingle a little. Why are you wanting a random integer value to store as your App_ID? Is there a reason why you can't have a sequential integer, such as an IDENTITY value?

    Alternatively, is there a reason you can't use a UNIQUE_IDENTIFIER, generated with NEWSEQUENTIALID() (which isn't quite guaranteed to be unique, but this should only be an issue if you move your database to a different machine).

    I'm just wondering, as I'm curious as to your reasoning. Note that you'll require an index on Event_Person_Info(App_ID) to avoid table scans when using NewBeeSQL's code.

    Also, just looking at the code in your begin/end: you're updating all rows where app_id is null. If you've inserted two rows, then whoever generates the app_id first may end up updating both rows (depending on transactional consistency).

  • Hi Guys,

    In simple terms I am trying to generate random numbers and store them in AppID column. But every row must have unique numbers. The algorithm would be:

    1)Generate random numbers.

    2)Check if the random number exists in the AppID column

    3)If exists, then go back to step 1. If not then continue to step 4

    4)Add the random number to AppID column.

    So there is a loop inside.

    Any help would greatly appreciated.

    Thanks in Advance

  • I'd like to have you answer Jim's question. Typically a random number isn't used, and I suspect that you'll get yourself into trouble without understanding why. If this is a PK and clustered, you don't want a random number.

    If it's not, what's the purpose that a sequential number wouldn't serve?

  • @steve-2: Actually what I need is unique number in AppID column, as a new row is entered.

  • tasnim.siddiqi (7/11/2010)


    @Steve: Actually what I need is unique number in AppID column, as a new row is entered.

    As previously stated , an identity column sounds like the job.



    Clear Sky SQL
    My Blog[/url]

  • An identity property and a unique index on this column will work. Trying to build the work for a random number is wasted effort. It's not needed.

  • Dave Ballantyne (7/12/2010)


    ...

    As previously stated , an identity column sounds like the job.

    Steve Jones - Editor (7/12/2010)


    An identity property and a unique index on this column will work. Trying to build the work for a random number is wasted effort. It's not needed.

    Use the above very rightfull advises untill Joe Celko see this post...

    😀

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Hey thanks a lot Guys.

    I still wanted to use random generation, because I am a C programmer and was expecting to achieve same objectives using C codes and SQL. Basically, I wanted to see whether the same thing could also be done using SQL.

    In C, one can generate random numbers in an array, and check if that random number exists, if exists then dont add to array, if not then add to array. If same thing could be done using SQL for a table column, it would have been awesome.

    Anyways, Thanks for the inputs.

  • Here's a different way to look at that problem... even if no likely use in real world

    Build a table with whatever range of numbers you need.

    Then

    select top (@NbRowsNeeded) * FROM #table order by NEWID()

  • tasnim.siddiqi (7/12/2010)


    I still wanted to use random generation, because I am a C programmer and was expecting to achieve same objectives using C codes and SQL. ....

    If you want to achieve a well performing system , you will have to stop thinking like that.

    You can generate a random number easily, you can even generate a million random numbers easily.

    The problem is that you now have to compare those random number against your already generated numbers. That takes time and resources.



    Clear Sky SQL
    My Blog[/url]

  • Hi,

    Try RAND() in sql

    Regards,
    Gayathri 🙂

  • For comparison purposes, this is one possible row-at-a-time solution:

    -- A temporary table for demonstration purposes

    CREATE TABLE #EventPersonInfo

    (

    epi_key INTEGER IDENTITY PRIMARY KEY, -- dummy column

    app_id INTEGER NULL -- column of interest

    );

    -- Add 5 rows

    INSERT #EventPersonInfo DEFAULT VALUES;

    INSERT #EventPersonInfo DEFAULT VALUES;

    INSERT #EventPersonInfo DEFAULT VALUES;

    INSERT #EventPersonInfo DEFAULT VALUES;

    INSERT #EventPersonInfo DEFAULT VALUES;

    IF SERVERPROPERTY('ProductVersion') > N'10.0'

    BEGIN

    -- Filtered indexes (SQL 2008 onward)

    CREATE UNIQUE INDEX uq1

    ON #EventPersonInfo (app_id)

    WHERE app_id IS NOT NULL;

    CREATE INDEX nc1

    ON #EventPersonInfo (app_id)

    WHERE app_id IS NULL;

    END;

    DECLARE @continue BIT; -- Flag

    DECLARE @temp INTEGER; -- Our random number

    SET @continue = 'true';

    -- Loop

    WHILE (1 = 1)

    BEGIN

    -- Get a new random number

    SET @temp = ABS(CHECKSUM(NEWID()));

    BEGIN TRY

    -- Start a transaction

    BEGIN TRANSACTION;

    -- Check that our random value does not already exist

    -- UPDLOCK and HOLDLOCK avoid concurrency problems

    IF NOT EXISTS (SELECT * FROM #EventPersonInfo E WITH (UPDLOCK, HOLDLOCK) WHERE app_id = @temp)

    BEGIN

    -- Update the first row we find with a NULL app_id

    UPDATE TOP (1) #EventPersonInfo

    SET app_id = @temp

    WHERE app_id IS NULL;

    -- Flag set false if we updated no rows

    SET @continue = @@ROWCOUNT;

    END;

    -- Success

    COMMIT TRANSACTION;

    END TRY

    BEGIN CATCH

    -- Rollback the transaction if it is still open

    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

    -- Echo the error message

    PRINT ERROR_MESSAGE();

    END CATCH;

    -- Break out of the loop if we have finished

    IF @continue = 'false' BREAK;

    END;

    -- Show the results

    SELECT *

    FROM #EventPersonInfo

    ORDER BY epi_key ASC;

    -- Tidy up

    DROP TABLE #EventPersonInfo;

  • This one uses a cursor:

    DECLARE @temp INTEGER; -- Our random number

    DECLARE @dummy INTEGER;

    DECLARE curNullAppIDs CURSOR LOCAL

    SCROLL DYNAMIC

    SCROLL_LOCKS TYPE_WARNING

    FOR SELECT app_id

    FROM #EventPersonInfo

    WHERE app_id IS NULL

    FOR UPDATE OF app_id;

    OPEN curNullAppIDs;

    -- Loop

    WHILE (1 = 1)

    BEGIN

    -- Position to the first row with a NULL app_id

    FETCH FIRST FROM curNullAppIDs INTO @dummy;

    IF @@FETCH_STATUS = -2 CONTINUE; -- Missing row

    IF @@FETCH_STATUS = -1 BREAK; -- End of data

    -- Get a new random number

    SET @temp = ABS(CHECKSUM(NEWID()));

    -- Ensure another row does not already use this random number

    WHILE EXISTS (SELECT * FROM #EventPersonInfo WITH (UPDLOCK, HOLDLOCK) WHERE app_id = @temp)

    SET @temp = ABS(CHECKSUM(NEWID()));

    -- Do the update

    UPDATE #EventPersonInfo

    SET app_id = @temp

    WHERE CURRENT OF curNullAppIDs;

    END;

    CLOSE curNullAppIDs;

    DEALLOCATE curNullAppIDs;

Viewing 15 posts - 1 through 15 (of 66 total)

You must be logged in to reply to this topic. Login to reply