SQL: Order randomly when inserting objects to a table
- by Ekaterina
I have an UDF that selects top 6 objects from a table (with a union - code below) and inserts it into another table. (btw SQL 2005)
So I paste the UDF below and what the code does is:
selects objects for a specific city and add a level to those (from table Europe)
union that selection with a selection from the same table for objects that are from the same country and add a level to those
From the union, selection is made to get top 6 objects, order by level, so the objects from the same city will be first, and if there aren't any available, then objects from the same country will be returned from the selection.
And my problem is, that I want to make a random selection to get random objects from table Europe, but because I insert the result of my selection into a table, I can't use order by newid() or rand() function because they are time-dependent, so I get the following errors:
Invalid use of side-effecting or time-dependent operator in 'newid' within a function.
Invalid use of side-effecting or time-dependent operator in 'rand' within a function.
UDF:
ALTER FUNCTION [dbo].[Objects] (@id uniqueidentifier)
RETURNS @objects TABLE
(
ObjectId uniqueidentifier NOT NULL,
InternalId uniqueidentifier NOT NULL
)
AS
BEGIN
declare @city varchar(50)
declare @country int
select @city = city,
@country = country
from Europe
where internalId = @id
insert @objects
select @id, internalId from
(
select distinct top 6 [level], internalId from
(
select top 6 1 as [level], internalId
from Europe N4
where N4.city = @city
and N4.internalId != @id
union select top 6 2 as [level], internalId
from Europe N5
where N5.countryId = @country
and N5.internalId != @id
) as selection_1
order by [level]
) as selection_2
return
END
If you have fresh ideas, please share them with me.
(Just please, don't suggest to order by newid() or to add a column rand() with seed DateTime (by ms or sthg), because that won't work.)
