MSSQLTips

http://www.mssqltips.com/tip.asp?tip=1518

Its a very handy utility but the part that caught my eye is assigning each column for each row of a result set. Is this just one of those unfortunate requirements when translatng from a collection to a recordset?

 I remember having to do this in VBA way back when and although it always worked, its slow work. Are there any functions that would do this automatically, cycling through a collection's data elements and automatically creating recordset and writing to it?

 THAT would be an incredibly useful library function.

 Regards

AndreQ

As far as I know you have to do it this way.  You can also return a result set from a function (i.e. a table-valued function) using the CLR.  It is a bit more elegant (well maybe) but you still have to manually assign the values to the result set.  I have done a tip on that; it should be published soon.

What you do is create a function (let's call it A) that retrieves the data you want to return as a result set and returns it as a collection class that implements the IEnumerable interface.  Then you have a companion function (let's call it B) that has an output parameter for each column in the result set.  When the CLR function is called it invokes A once then iterates over the collection calling B one time for each element in the collection.

Here are the 2 functions:

/* THIS IS 'A' */

    [SqlFunction(SystemDataAccess = SystemDataAccessKind.Read, FillRowMethodName = "GetListInfo")]
    public static IEnumerable GetListCollection(SqlString url)
    {
        DataTable t = new DataTable();
        WindowsImpersonationContext ctx = null;

        WindowsIdentity id = SqlContext.WindowsIdentity;

        try
        {
            ctx = id.Impersonate();

            WSS.Lists svc = new WSS.Lists();
            svc.Url = url.ToString();

            //svc.Credentials = new NetworkCredential("barley", "pass@word1", "VS");
            svc.Credentials = CredentialCache.DefaultNetworkCredentials;
            XmlNode node = svc.GetListCollection();
            XmlTextReader rdr = new XmlTextReader(node.OuterXml,
                                        XmlNodeType.Element, null);
            DataSet ds = new DataSet();
            ds.ReadXml(rdr);
            t = ds.Tables[0];
        }
        finally
        {
            if (ctx != null)
                ctx.Undo();
        }

        return t.Rows;
    }

/* THIS IS 'B' */
    public static void GetListInfo(
                        object obj,
                        out SqlString name,
                        out SqlString title,
                        out SqlString url )
    {
        DataRow r = (DataRow)obj;
        name = new SqlString(r["Name"].ToString());
        title = new SqlString(r["Title"].ToString());
        url = new SqlString(r["DefaultViewUrl"].ToString());
    }