We help IT Professionals succeed at work.

insert does not work with newid()

hi, can someone make this query works,

insert int @table1(id)
set rowcount 1
select t1.id from tb_test
order by newid()

that's any simply example of my quer. but if we do the syntax for this one i can do on mine.
Comment
Watch Question

Top Expert 2011

Commented:
You probably need something more like this:

set rowcount 1
insert into @table1(id)
select t1.id from tb_test t1
order by newid()

not real sure what you are trying to do though

Author

Commented:
i am trying to put this inside an function, but it does not execute, shows an error saying:

invalid use of a side-effecting operator 'SET ROW COUNT' whithin a function .
invalid use of a side-effecting operator 'newid' within a function.


:(
AnujSQL Server DBA
Top Expert 2011
Commented:
In sql server SET options which can change Server level or transaction level settings are prohibited in UDFs. So you cannot use SET option inside function, Consider using Procedures.

Author

Commented:
it needs to be an function, but how do i select random rows without using rowcount?
SET ROW COUNT and newid both are not allowed in function
you can use both of this in store procedure.

Try using store procedure with output parameter.