July 9, 2010 at 3:53 am
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!
July 9, 2010 at 4:27 am
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)
July 9, 2010 at 4:56 am
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).
July 11, 2010 at 8:10 pm
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
July 11, 2010 at 8:51 pm
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?
July 12, 2010 at 1:28 am
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.
July 12, 2010 at 8:24 am
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.
July 12, 2010 at 8:43 am
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...
😀
July 12, 2010 at 7:20 pm
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.
July 12, 2010 at 8:23 pm
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()
July 12, 2010 at 11:58 pm
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.
July 13, 2010 at 12:09 am
Hi,
Try RAND() in sql
Regards,
Gayathri 🙂
July 13, 2010 at 5:26 am
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;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 13, 2010 at 5:55 am
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;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 1 through 15 (of 66 total)
You must be logged in to reply to this topic. Login to reply