Random number generation in sql

Vijay Yadav

15y2.2kSQL

I want employees id to be fetched randomly by using the blow query

select Top 2 SessionId from Flash_Temp2
order by NEWID()

but instead of top 2, i want to pass it in a variable as its depend on the situation for ex:

declare @id int
set @id=3
select Top @id SessionId from Flash_Temp2
order by NEWID()

it givs following error:
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '@id'.

12 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes15y ago
Accepted answer
Try:

declare @id int
set @id=3
set rowcount @id
select * SessionId from Flash_Temp2
order by NEWID()
Vulpes15y ago
If you want to loop through the Emp_Id values, then I'd use a cursor and while statement as Zoran suggested.

The code will be something like this:

declare @id int

declare Employee_Cursor cursor for
select EmpId from Employee

open Employee_Cursor

fetch next from Employee_Cursor into @id

while @@fetch_status = 0
begin
set rowcount @id
select SessionId from Flash_Temp2
order by NEWID()
// do something with SessionId
fetch next from Employee_Cursor into @id
end

close Employee_Cursor
deallocate Employee_Cursor
Vijay Yadav15y ago
Hi Vulpes,
Can you please check the below link, may be here i didn't ask the question properly
http://www.c-sharpcorner.com/Forums/Thread/130225/assign-single-varible-a-multiple-rows-using-select-query-in.aspx
Please check it..
Vulpes15y ago
Well, at the end of the day, you're going to have to set rowcount to @id if you want to return a variable number of rows from the query.

It's therefore just a matter of devising code which assigns the appropriate value to @id before you set the rowcount.

I don't think I (or Suthish) can help you further without knowing how this will be determined in particular circumstances.
Vijay Yadav15y ago
yes i have accepted the answer but i have one more query which is in my last thread. Anyway i would start new thread. Do you got my problem?
Suthish Nair15y ago
Vijay, is this thread resolved or active. becoz you accepted one of the post as answer. Or open a new thread.
Vijay Yadav15y ago
Ok! but set @id=3 is not constant this may vary depending upon the situation say at one time it can be 0, 5 10 or anything.. ( i am calculating some value and the output of that i am giving to @id). Any other idea!!!
Vulpes15y ago
I was thinking that you could replace this line:

set @id=3

with something like this:

set @id =
case
when someCondition1 then 1
when someCondition2 then 2
when someCondition3 then 3
end

so @id would then have an apropriate value when you hit the line:

set rowcount @id
Vijay Yadav15y ago
Vulpes,
Considering our example,
declare @id int
set @id=3
set rowcount @id
select * SessionId from Flash_Temp2
order by NEWID()
How to save this in varibale i didn't get it.
Please help
Vulpes15y ago
You might be able to use a CASE expression:

set @id =
case
when someCondition1 then 1
when someCondition2 then 2
when someCondition3 then 3
end
Vijay Yadav15y ago
Hi Vulpes,
One more doubt i want to say all that in 3 different variable i want to know how to do as there is nothing like array that we have in programming language. Any suggestion?
Vijay Yadav15y ago
Thanks again!!! :)