Rand () to get rand value from temp table
-
Below is code that I am using with hopes to get a random value from a temp table. It isn't giving me a value...so I am doing something wrong. Two options are there with one commented out. Any help would be greatly appreciated.
I am trying to get a random value for automatically populated tables so that it looks clinically correct and not always bring back the same test result.
declare @SelfTestResult varchar(50)
Declare @tmpResults table
(
RowID integer identity(1,1),
Result varchar(50)
 
insert into @tmpResults (Result) values ('Normal')
insert into @tmpResults (Result) values ('Diminished Sensation')
insert into @tmpResults (Result) values ('Visible Ulcer')
select * from @tmpResults
--Select Result from @tmpResults where RowID = Rand()*(3-1)+1
Set @SelfTestResult = convert(varchar(50),(Select Result from @tmpResults where RowID = Rand()*(3-1)+1))
print 'Result: ' + @SelfTestResult
-
select top 1 Result
from @tmpResults
order by newid()?
-
where RowID = convert(int,(Rand()*(3-1)+1))
Yours before was returning a floating point value 2.38898 (or some other random float)
That's why you're not getting anything back.
You need to understand datatypes and how they are represented.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply