Sometimes it is necessary to send a complete collection as argument to a stored procedure. I recantley had a situation where I wanted to get information from the database dependeing on which windows groups/roles the user belonged to. I think that the easiest way to solve this problem is to send the collection as XML (if you don't use SQL Server 2008) and in the sp convert that xml to a table. This solution have worked great for me many times, but I always find myself redoing the solution and to do that I need to search and read some in the help files, so now I thought I should document it here instead.

This is the solution that I came up with. First the stored procedure:

CREATE PROCEDURE ResursOperationGetForRoller
    --XML-representation of the roles
    --<roles><role>Everyone</role><role>BUITIN\Users</role>[...]</roles>
    @rolesDoc xml 
AS
BEGIN

    DECLARE @idoc int 
    DECLARE @roles TABLE (RoleName nvarchar(255))

    SET NOCOUNT ON;

    -- Fill the tabel from the XML-document
    EXEC sp_xml_preparedocument @idoc OUTPUT, @rolesDoc

    INSERT INTO @roles
    SELECT    *
    FROM       OPENXML (@idoc, '/Roles/Role',2)
                WITH (RoleName  nvarchar(255) '.')

    EXEC sp_xml_removedocument @idoc

    --Do something with the @rollTabell
    
END
GO

Most of the code explains itself, but '.' in the WITH-clause might be worth a word. The dot is an XPath expression that means that I take the value of the current element. Now when the result is in a tabel variable it easy to use iin joins and other common T-SQL, so lets move over to the VB code showing you how to send the parameter.

command.Parameters.Add("rolesDoc", SqlDbType.Xml).Value = GetCurrentUserRolesAsXml()

Private Shared Function GetCurrentUserRolesAsXml() As String
    Dim roles As List(Of String) = GetCurrentUserRolesAsList()
    Dim xml As New StringBuilder("<Roles>", roles.Count * 100)

    For Each role As String In roles
        xml.AppendFormat("<Role>{0}</Role>", role)
    Next

    xml.Append("</Roles>")

    Return xml.ToString()
End Function

In this sample I'm just using a StringBuilder to create the xml, but of course other valid solutions exists, for example using serialization if the xml document is more complex than this simple example.