Random Number

Apr 15, 2002

Generates a true random number from xxx to zzz. Uses a table variable so it all depends on the boundaries you specify how long it will take.

create procedure random_number
@lower int = 0,
@upper int = 256,
@number int = null output
as
set nocount on

declare @numbers table (number_id int identity(1,1), value int)

if @lower > @upper
begin
set @number = @lower
set @lower = @upper
set @upper = @number
end

set @number = rand((datepart(mm, getdate()) * 100000) + (datepart(ss, getdate()) * 1000) + datepart(ms, getdate()))

while (select count(*)
from @numbers) < (@upper - @lower + 1)
begin
insert into @numbers (value)
select (@upper - @lower + 1) * rand() + @lower
end

select @number = value
from @numbers
order by newid()
go

View 16 Replies


ADVERTISEMENT

Random Number

Oct 8, 2005

I need to update an integer column in my table with a random number. Here's what I've done so far (with the table name and column names changed):

UPDATE dbo.MyTable
SET Column1 = RAND() * 1000000

My question is, how come the values in my column all have the same value? I thought the RAND function gives out a random number? How can I update my table to generate random numbers?

Thanks in advance.

View 3 Replies View Related

Update To A Random Number?

Nov 27, 2004

Hi,

How can I update my users table and set the password field to a random number or string.

eg.

update tbl_users set password='a way to generate a random number say between 2000 and 100000 for example'

even better would be a randomly generated alphanumeric string.

All help much appreciated.

Cheers,

RG

View 2 Replies View Related

Random Number Generator

Feb 23, 2005

i have an application that uses a table for login access, for security reason i need to get a random numbers in this table daily. table consist of just two columns, userid and password. This table will be printed out on the web for users to get valid username and pw daily.
I m thinking about a job or dts package that will run once daily with a sql script to generate random number (referable 5 digit-letters and numbers), delete the table and re-create the table with same set column names and insert the random values in username and pw column.
Will appreciate help on this

Thanks

View 1 Replies View Related

How To Generate Random Number For The Value In Th

Aug 2, 2007

How to create random number for the value in other colum. Please help and urgent

I have a table with 4 column ( ID, Ori_Quantity, Rand_Quantity, Location)

If Ori_quantity < 5000 then
Rand_quanty as qty = I want Random number within 100
Else If Ori_quantity = 0 or < 10 then
Rand_quanty as qty = I want Random number within 7
End if

From tblname where Location = 'DAS'

I have around 2500 field. So when I run the query I expect the result should be
like below

Ori_Quantity, Rand_Quantity
------------ -------------
4000 90
2986 57
2500 89

I don't want to pass any parameter and I am new. Please help

View 7 Replies View Related

Random Number-9 Digits Only

Mar 25, 2008

Hi,

I need to generate random numbers that will always contain 9 digits.
How can I do that?
Thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

View 1 Replies View Related

How To Gnerate A Random ID Number

Jun 9, 2007

I just wana make a random id number based on4 digits-for examples??Thanks in AdvanceCh.Yallin

View 21 Replies View Related

Random Number Generator

Sep 28, 2007

In a Name table, I need to generate unique 6 digit random numbers in a field called UniqueID for all records that have the ID field populated.
I will need to run this script periodically. It is critical that any prevoiusly assigned UniqueIDs do not change and only fields that have an empty UniqueID field are updated. I need to preserve the historical mapping of the existing IDs to the ongoing assignment of UniqueIDs.

Regards,

Polar Bear

View 3 Replies View Related

Build Random Phone Number.

Jan 15, 2005

I'm building a test database and I need to randomly create phone numbers in the format xxxxxxxxxx

I have 5000 contacts and need to generate fake phone numbers for them. anyone know how I can write an update query to do this?

ScAndal

View 4 Replies View Related

Generate Random And Unique Number Within Sql ?

Dec 11, 2007

I need to generate a random 10 digit alphanumeric string that is also unique within a table. My application will be calling a stored procedure to insert this number into the table. This number will be associated with a id from another table. Is it better to generate the random number within sql (and perform the lookup at the same time), then just pass the number back to the calling application ?

If the calling application generates the number, it will also need to make a call to check if its unique. So im thinking it would be best to simply have sql generate this random number, check the number against the table and then insert the new record.

thoughts ?

View 5 Replies View Related

Transact SQL :: Set Unique Random Number

Dec 1, 2015

Lets say we have a table (tblProducts)

ID   Item             RandomNumber
-------------------------------------------
1    JEANS                      1234567
2    SHIRT                    72813550
3    HOOD                             Null
4    TROUSER               72191839
5    BLAZER                              0

I want to perform a query so that SQL should look for RandomNumber Values and set a Unique Random Number Where RandomNumber Value is Null or 0.So I have got a solution as one of the MSDN Member shared the below query

select id,item,RandomNumber=Case when RandomNumber=0 then (select floor(rand()*100000000-1))
when RandomNumber is null then (select floor(rand()*100000000-1))
else RandomNumber end from tblProducts

So, can you all confirm me, that performing this query ensures that if a Value is assigned to one of the Item in RandomNumber Column, that value will not be assignend to any other Item in RandoNumberColumn.

View 15 Replies View Related

Random Number Procedure Problems!

Sep 7, 2006

Hey Guys, I have a quick stored
procedure question for you. I created a procedure that returns a randomly
generated number.

I know there is a built in
Rand() function but I need to be able to
specify the range. Anyways, it€™s returning multiple numbers (1 for each row in
the tempTable)
But I just want one number returned.
I tried using MAX( ) but that€™s not a good way of doing it.


My question is, how do I return just
one record or number?

Thanks
Fellas,

Chris


ALTER PROCEDURE
dbo.spx_GetRandomNum

AS

DECLARE @Start_Num int

DECLARE @End_Num int

DECLARE @ReturnValue
int
DECLARE @RecNum
int
DECLARE @CurrentRec
int
DECLARE @MyCount
int

SET @Start_Num=1

SET
@End_Num=100


CREATE TABLE #tblTemp (CurrentRecNo
int IDENTITY(1,1), RandomNumber int null)

SET @MyCount = ( @End_Num -
@Start_Num ) + 1
SET @CurrentRec = 1


WHILE @CurrentRec <= @MyCount


BEGIN
SET @RecNum
= CAST((RAND() * @MyCount) AS int) + @Start_Num

INSERT INTO
#tblTemp (RandomNumber) VALUES (@RecNum)
SET
@CurrentRec = @CurrentRec + 1

END

SET
@ReturnValue = (SELECT RandomNumber FROM
#tblTemp)

DROP TABLE #tblTemp


RETURN
@ReturnValue
GO

View 4 Replies View Related

How To Assign Random Number To One Column For 500 Records?

Aug 4, 2005

I try to set up a testing sample table which contain one  integer project_ID field for table Sample around 500 records, and want the project_ID to be random number within 1 to 99, how to implement script todo it?
thanks!

View 1 Replies View Related

How To Generate Random UNIQUE 13 Digit Number?

Aug 22, 2007

Hi

I have a sql procedure. I need to create UNIQUE random 13 digit number to use for barcode.

How do I generate 13 digit UNIQUE random in sql procedure?

Advance thanks

View 20 Replies View Related

Transact SQL :: How To Assign Random Number Between 0 And 1 To A Record

Nov 17, 2015

I need to assign a random number between 0 and 1 to all eligible cases of cancer. 

I have a file of records like:
patientid
tumid
site

How can I assign a random number to each record?

View 10 Replies View Related

SQL Server 2014 :: Generate Random Number In Range

Oct 3, 2015

I have a function which generate random number in range :

CREATE FUNCTION Func_Rand
(
@MAX BIGINT ,
@UNIQID UNIQUEIDENTIFIER
)
RETURNS BIGINT
AS
BEGIN
RETURN (ABS(CHECKSUM(@UNIQID)) % @MAX)+1
END
GO

If you run this script you always get result between 1 and 4

SELECT dbo.Func_Rand(4, NEWID())

BUT, in this script sometimes have no result !!!!!!! why??

SELECT 'aa'
WHERE dbo.Func_Rand(4, NEWID()) IN ( 1, 2, 3, 4 )

View 2 Replies View Related

Returning Random Records And NOT Similar (random Questions)

Jul 20, 2005

Hi,I need to extract randomly 5 records from the table "Questions". Now I useSELECT TOP 5 FROM Questions ORDERBY NEWID()And it works. The problem is that I need an additional thing: if SQLextracts record with ID=4, then it should not extract record with ID=9,because they are similar. I mean, I'd like something to tell SQL that if itextracts some questions, then it SHOULD NOT extract other ones.How can I do it?Thanks!Luke

View 1 Replies View Related

URGENT - Random 10 From Random 20 (2 Tbls)

Oct 14, 2004

I'm using ASP and SQL Serv 2000. What I need to get from 2 tables (company & customers) is random 10 customers from random 20 comp.
Anyone got an idea how to do this??? I've spent 2 days trying to get stored proc. or T-SQL to work, but nothing good came out of it. I can get 1 comp and 10 cust, but not a grouped list of 20 comp. w/ 10 cust. each.

Help is greatly appreciated.

View 1 Replies View Related

The Number Of Requests For XXXServerXXXUser Has Exceeded The Maximum Number Allowed For A Single User

Oct 15, 2007



I have created a local user on Report Server Computer and the user has the administrative rights.
When i try to connect Report Server (http://xxx.xxx.xxx.xxx/reportserver) with this user's credantials. (ReportServer directory security is set -only- to Basic Authentication. ).
I get the following error.


Reporting Services Error
--------------------------------------------------------------------------------

The number of requests for "XXXServerXXXUser" has exceeded the maximum number allowed for a single user.
--------------------------------------------------------------------------------
SQL Server Reporting Services


Then i try to login using a different user with administrative rights on the machine, i can logon successfully.
The system is up for a month but this problem occured today?!? What could be the problem?!?

View 2 Replies View Related

How To Enter More Number Of Rows In A Table Having More Number Of Columns At A Time

Sep 24, 2007

Hi

I want to enter rows into a table having more number of columns

For example : I have one employee table having columns (name ,address,salary etc )
then, how can i enter 100 employees data at a time ?

Suppose i am having my data in .txt file (or ) in .xls

( SQL Server 2005)

View 1 Replies View Related

Transact SQL :: How To Select A Number Less Or Equal Than A Supplied Number

Jun 23, 2015

Got this query and I need the following result;

declare @NumberToCompareTo int
set @NumberToCompareTo = 8
declare @table table
(
number int
)
insert into @table 
select 4

[Code] ....

The query selects 4 and 5 of course. Now what I'm looking for is to retrieve the number less or equal to @NumberToCompareTo, I mean the most immediate less number than the parameter. So in this case 5

View 4 Replies View Related

How To Change A Decimal Number To Percent Format Number?

Oct 8, 2006

in my sql, i want to change a decimal number to percent format number, just so it is convenient for users. for example there is a decimal number 0.98, i want to change it to 98%, how can i complete it?

thks

View 4 Replies View Related

Limitations In Term Of Number Of Tasks And Number Of Columns

Jun 5, 2007

Hi,

I am currently designing a SSIS package to integrate data into a data warehouse fact table. This fact table has about 70 columns among which 17 are foreign keys for dimension tables.

To insert data in that table, I have to make several transformations and lookups. Given the fact that the lookups I have to make are a little complicated, I have about 70 tasks in my Data Flow.
I know it's a lot, but I can't find a way to make it simpler. It seems I really need all these tasks.

Now, the problem is that every new action I try to make on the package takes a lot of time. At design time, everything is very slow. My processor is eavily loaded each time I change a single setting in one of the tasks, and executing the package in debug mode takes for ages. If I take a look at the size of my package file on disk, it's more than 3MB.

Hence my question : Are there any limitations in terms of number of columns or number of tasks that can be processed within a Data Flow ?

If not, then do you have any idea why it's so slow ?

Thanks in advance for any answer.

View 1 Replies View Related

Random Row

Jul 20, 2005

Hi there.I need to fetch ONE random row from many. How can i get it? I try toexecute "SELECT field.table FROM table ORDER by RAND()" no effect.Thank you.

View 1 Replies View Related

Select Random Row In Sql

Apr 11, 2007

Hi,
i wanna fetch random data using sql query. is there any query that returns random row? Please help me.. i found some but they didnt work..

View 8 Replies View Related

How To Do A Random Select

Nov 10, 2000

Hy,
I nead to make a stored procedure in order to add a random record set.
This record set is used to write in an ASP page a list which is automatically modified everytime we reload the page.

Thanks !

View 1 Replies View Related

Random Records

Nov 16, 1999

Hi,

I'm trying to use the following query to select two random records from my database. Do you have any ideas of why the recrand field will not change? I am using MS Sql server 7.. Please email mark@dtdesign.com ..Cheers Mark

SELECT TOP 2 mytable.id AS RECID, RAND(mytable.id) AS recrand
FROM mytable
ORDER BY recrand

View 3 Replies View Related

DTS Bug? (or Random Feature?)

Jul 16, 1999

Has anyone noticed that if you created DTS package and try to
change connection properties (i.e. change DSN or redirect
DTS to different server or Database), as soon you click OK
it does not save new password and hence does not work
anymore. In my case to move Database from Development to
Production server I would have to recreate all DTS packages.
Is there any way around it?

Any ideas greatly appreciated.

View 1 Replies View Related

Random Numbers.

Jun 22, 2001

I am trying to get random numbers to have a unique value for different processes, then I can identify each process. What happens is that I use rand() function without seed, so I got my random numbers, but after shutting down SQLServer and try to get again another random number after booting up, the same series of random numbers is given again and again. So if anyone knows how I can get unique values,even though reseting the server, and using random function or any other method which automatically provides unique values,I'll really appreciate it if you let me know it.

This is the function: select rand()

Alberto.

View 2 Replies View Related

Random Values

Dec 24, 2001

Is there a way to write an SQL statement to choose random values from a particular select statement..for example:
select * from address
I was thinking, if there was a way to use an include statement for it like:
select * from address where id IN ('generates some random values')

thanks in advance

View 2 Replies View Related

Last Day In A Random Month

Mar 7, 2006

So i've got to generate these queries that go from the first of a one month to the last day of a month. the user provides the starting and ending months.

What I was wondering was is there an easy way to set the last day of the month in sql without having to go in and hard code which months end on the 30 and which ones end on the 31.

View 2 Replies View Related

Random Values In MS SQL,

Jan 1, 2005

Hello guys , I am new here,

Well, I am moving my greetings script from MS Access to MS SQL, here is what I was using in MS Access.

See this screenshot: Click Here
Well, I've imported the data from access database, but I don't know how to do the AutoNumber in MS SQL,

Also I don't know how can I add that Random thing *See the arrow in the screenshot*

Thanks in advance

View 2 Replies View Related

Random Varchar(10)

Jan 27, 2006

How can I have display random value for field varchar(10).

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved