User Defined Aggregate Problem

I am trying to get a random value from a select statement. The only way I thought to do this would be to make a User Defined Aggregate Function at returns one of the rows at random. For example MIN(column) return the minimum value of that column, MAX(column) returns the maximum. I would like something like RANDOM(column) to return one of the values at random.

For example: select RANDOM(Name) from Names

So I went and created a user defined aggregate function:




Code Snippet

Imports System

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server



<Serializable()> _

<Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)> _

Public Structure GetRandom

Public s As SqlString

Public x As Integer

Public Sub Init()

x = 0

Randomize()

End Sub

Public Sub Accumulate(ByVal value As SqlString)

s = New SqlString(s.ToString & "|" & value.ToString)

x = x + 1

End Sub

Public Sub Merge(ByVal value As GetRandom)

End Sub

Public Function Terminate() As SqlString

Dim d() As String = {"|"}

Return New SqlString(s.ToString.Split(d, StringSplitOptions.RemoveEmptyEntries)(Rnd() * x))

End Function

End Structure



The problem is that it wont deploy to the SQL Server because the varible "s" is not serilizable. Here is the error message:

Type "GetRandomFunction.GetRandom.GetRandom" is marked for native serialization, but field "s" of type "GetRandomFunction.GetRandom.GetRandom" is not valid for native serialization.

Can any help me with this? Is there any other way of storing the strings that will work?

Thanks,
Stuart Fox