Check for Random Number ,Insert if not exists

Transact-SQL
,
1

HI , I need to generate a random number to use as a barcode , The number has to go into a XML (UDF) for which i have a SP . I can generate a number and the cursor runs without error . but i clearly missing something because its not inserting anything .Help as always appreciated

DECLARE @Location         bigint
DECLARE @Random numeric
DECLARE @RandomField  VARCHAR(10)
--set @Location = [Identifier]
SET @Random = convert(numeric(15,0),rand() * 999999999999999) 
SET @RandomField  = 'AUTOCT' + CAST(@Random as varchar(15))
IF NOT EXISTS (select LocBarcode from wv_LocationsUDFs where LocBarcode = @RandomField and Identifier = @Location)
BEGIN

Declare Locations_Cursor Cursor
for

 select
location 
,(select 1 from wv_LocationsUDFs where LocBarcode = @Random)
from Locations


Open Locations_Cursor
FETCH NEXT FROM Locations_Cursor INTO @Location , @Random

WHILE @@FETCH_STATUS = 0
    BEGIN
 EXEC dbo.wsp_CustomValuesUpdate
		@RecordNumber = @Location,
		@CustomFieldTable = 'Locations',
		@CustomFieldId = 'locBarcode',
		@ParamNvarchar = @Random,
		@ParamDate = NULL,
		@ParamDecimal = NULL,
		@ParamInteger = NULL,
		@ParamLong = NULL,
		@ParamBit = NULL,
		@LastModifiedUser = '[UserName]'

FETCH NEXT FROM Locations_Cursor INTO @Location , @RandomField

		End

		close Locations_Cursor


END
2

Maybe that random number does exist and hence not inserting records? and it looks like @localtor is declared but never given a value hence it is null? Is that by design?

DECLARE @Location         bigint
--set @Location = [Identifier]

IF NOT EXISTS (select LocBarcode 
from wv_LocationsUDFs where LocBarcode = @RandomField 
and Identifier = @Location) <====You are using it here as null
BEGIN
3

I only have 1 random number in one Record of 300 records so it should populate the remaining 299 but cant see where i'm wrong

4

hi

++++++++++++++++++++++++++++++++++++++++++++++++++++++
first check if the 300 numbers are being generated .. if not fix it

then

check if the insert is happening for each random number

if not fix it
++++++++++++++++++++++++++++++++++++++++++++++

for the sake of testing do 3 numbers !! .. fix all things
then run for 300 numbers

5

There are several problems with this code...

@Random is generated one time - but is then updated/changed in the first fetch and set to 1.
@RandomField is used in the fetch once inside the loop - and that value will always be set to 1 or NULL because this code:

,(select 1 from wv_LocationsUDFs where LocBarcode = @Random)

It isn't clear what the purpose of the code is for - is this supposed to generate a random number and insert a different random number for each row in Locations? Or - is this supposed to insert a single row - or what?

1 Like
6

What I'm trying to achieve is a random number in each field but a check on the fields to ensure the random number has not been produced before . Ive just lost my way

7

This still isn't clear - you want a random number in each field? If you want a different random number in each column - which columns? Or - do you want a random number in the column ParamNvarchar for each row?

What are you trying to accomplish here?

8

why not use a uniqueidentifier?

1 Like
9

Hi yes I'm trying to get a unique number in each field using ParamNvarchar entry in the SP . I want to check A if there is already an entry and B that the new random number does not match any others already in any of the locbarcode fields . Locbarcode is and XML UDF soni have to use the SP to insert it .

I hope that makes sense :grimacing:

10

hi

can i remote desk top .. to your machine ..
we can chat on whats app Live ..

what you are trying to do is VERY VERY easy !!

only thing .. we will be going back and forth a lot of time waste !!