Random number generation in sql

Question

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'.

Vulpes · Answer

Try: declare @id int set @id=3 set rowcount @id select * SessionId from Flash_Temp2 order by NEWID()

Vulpes · Answer

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 Yadav · Answer

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..

Vulpes · Answer

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 Yadav · Answer

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 Nair · Answer

Vijay is this thread resolved or active. becoz you accepted one of the post as answer. Or open a new thread.

Vijay Yadav · Answer

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!!!

Vulpes · Answer

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 Yadav · Answer

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

Vulpes · Answer

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 Yadav · Answer

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 Yadav · Answer

Thanks again!!! :)