creating a .net CLR SQL Server 2005 Function to return result sets

Thanks tedbilly for the reply. I'm afraid I don't understand how that works based on those examples. I'm not seeing the link between the InitMethod and the FillRow method. This is what I came up with but it seems odd because I'm creating a datatable full of data which is really not what I need. I just want to stream out the results. Thoughts? Do I have to go through all the work of creating a datatable just so I can enumerate?

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Runtime.InteropServices

Partial Public Class UserDefinedFunctions

    Public Class TabularEventLog
        <SqlFunction(FillRowMethodName:="FillRow", systemdataaccess:=SystemDataAccessKind.Read)> _
        Public Shared Function InitMethod(ByVal UserId As Integer) As IEnumerable

            Dim outDT As New DataTable

            Using adp As New SqlDataAdapter("select fName, lName from SomeTable where userId = " & UserId, New SqlClient.SqlConnection())
                adp.Fill(outDT)
            End Using

            Return outDT.Rows
        End Function

        Public Shared Sub FillRow(ByVal obj As Object, <Out()> ByRef fName As SqlString, <Out()> ByRef lName As SqlString)
            Dim dr As DataRow = DirectCast(obj, DataRow)
            fName = New SqlString(CStr(dr.Item("fName")))
            lName = New SqlString(CStr(dr.Item("lName")))
        End Sub
    End Class

End Class

Well I answered your question literally, which was returning a table from a CLR udf Function and you asked about learning the capabilities of CLR functions which I gave you.

CLR functions can return scalar or non-scalar results.  So, you are returning one discrete value or a data set.  Stored procedures are basically the same.  You are returning values as discrete data types OR a data set.

Can you describe your business requirements?

tedbilly,

thank you very much for the response. Part of my question is below. The example you gave does not demonstrate how to do this. It's possable I'm not reading it correctly, if that's the case can you point out the section that talkes about doing this? Thank you again for your help!

How do I return a result set from a CLR function? Does anyone have any simple examples that pull data from the same database (not some other second data source like an RSS feed)

My requirement is to write a CLR UDF function that returns a result set from a query returning data from the database hosting the CLR UDF. I then want to join the results of that query with another query. Basicly exactly as I could with a standard UDF but with CLR.

Hmm, well then the link I gave you is correct. The example I have you shows you how to create a CLR UDF.

If you want the UDF to return a table from the database hosting it you do not need to create a CLR UDF.  You can create a standard UDF function.

Can you give me more details because I've given you everything you need to write a CLR UDF.  By details I mean the database schema and the join you need to do.

I thank you very much for your help tedbilly but Im afraid I dont see your solution.

Its possible Im not asking the correct question but as I look back over my previous responses and initial question I believe Im clearly asking how to return data from the host database from a CLR UDF.


At the end of my initial question there is this:

How do I return a result set from a CLR function? Does anyone have any simple examples that pull data from the same database (not some other second data source like an RSS feed)

You provided an example that did satisfy the first question, which was how to return a result set from a CLR function BUT it did exactly what I said I didnt want it to do in the second part which was to NOT pull data from a second data source, in your case the event log.

Now if the example you gave me does indeed pull data from the hosting database and returns it as a result set I think perhaps I need to give it another look, maybe you can point me to the right place in the article.

I mean no disrespect to you but Im not seeing your solution as fitting my question.

I looked at the example you gave again: http://msdn2.microsoft.com/en-us/library/ms131103.aspx and I do see that it says it wll return a result set BUT it returns a result set from the event log and not from the database. How can I return the result set generated from Select * from sometable where field='value'

that's what I was trying to do in my inital post. I failed but that's what I'm trying to do.

If you want to return a result set from the database you do not need a CLR UDF.  I've already mentioned this point but I think you missed it.  If you want to return data from the same database or SQL server as the UDF you do NOT need a CLR function.

The purpose of the CLR in SQL is to create custom SQL objects to do things that SQL cannot do.  A standard UDF function can return a table in the same database.

The following article has an example of how to return a table using a standard UDF
http://www.databasejournal.com/features/mssql/article.php/1438081

The following article explains where and when to use the CLR in SQL
http://www.microsoft.com/technet/prodtechnol/sql/2005/clr4dbas.mspx

Alright the business case... This is going to be long but perhaps you will see a better way to do this.

I have a table called Transaction, which has an integer primary key along with five foreign keys, which relate back to tables, which contain a single integer primary key along with other fields.

For example, one table is the name table that holds the specific name of the transaction.

Actually, this is just a simple implementation of a star schema.

Another of the foreign keys on the transaction table is UserId.

My goal was to create a view, which would return all transaction Ids which I could then use in other queries in a join situation. This view needed to take a user Id so it only returned those transactions owned by the specific user. So a view that uses a parameter, or a UDF.

One can imagine how easy this particular UDF was. Something along the lines of

Select id from transaction where userid = @userid

Now whenever I wanted to get some piece of data about a users transactions I could just use this UDF, passing in the UserId, and get a list of all transactions that fit. I use this view all over the place trusting that I will ONLY  get transactions for the given user.

Then some time later I wanted to extend the app I was working on to allow for the following:

The user was able to specify a dynamic set of criteria which would be applied and the ENTIRE SET of functionality the app offered would continue to work but with the new filtered results.

So I think to myself that should be easy. I have one single point where I pull data. My UDF takes a user ID and passes back all the data. What if I also passed in a varchar where clause into that procedure and dynamically returned the results. My query ended up being Select Id from transaction where userid = @userid and @WhereClause. Now in the UDF I needed to create a varchar to hold the new dynamic query and then use exec to return the results.

Oops I cant use exec in a UDF. So I thought I was stuck. Changing the entire app was not possible. I couldnt rewrite every single feature to take into account a dynamic where clause.

So I decided this might be a valid use for a CLR UDF. I knew this was slower then standard TSQL so I settled on a compromise. When the UDF was called with the dynamic where clause I would use the CLR UDF from inside the standard UDF but when the where clause was blank I could continue doing it the way I was already.

Therefore, the user will only see a slow down when they have these dynamic where clauses filtering all the data.

So I made the necessary adjustments to the UDF, applying an if/else that would return the correct values.

Now I know how to construct a query with the dynamic where clause. What I dont know is how to select data from my CLR function and return it to the caller.



I realize this is long and complicated but if you get through it and understand what Im trying to do Im more then willing to listen to advice you have. For example, do you know how to run a dynamic SQL statement from inside a UDF? Or do you know how to return a result set queried from the host database from a CLR UDF?


I fall back to my initial question again, just so everyone knews Im not holding the thread hostage.

How do I return a result set from a CLR function? Does anyone have any simple examples that pull data from the same database (not some other second data source like an RSS feed)

Thank you.
This is a lot of text, sorry for any typos.

What you are trying to do can be achieved in stored procedures or the business layer of your application.  You do not need a CLR and it is not well suited for your case.

I've solved your problem quickly and easily many times in transaction SQL using views or stored procedures.

I give up.  You have everything you need to solve this problem.

Stored Procedures: Yes that could be done but would require me to rewrite the app, which I said I didn't want to do. Or can the results of a stored procedure be used in joins in other queries (I believe no but I may be wrong)

Business Layer: Yes it could be done here but I don't want to return countless rows of data from the server only to have the BL filter them based on some logic. This is wasteful.
I'm not sure exactly what solution you offered other then saying the CLR was not the correct solution.

A number of times I asked a question, how to return a result set obtained from a query in the host database with a CLR function. If that can't be done let me know. If it can and you know how then tell me and I will award you the points.

The first example I have you showed how to write a CLR UDF and return a table.  The next example shows how to access the host database within a CLR stored procedure.  You'll have to combine the two.

http://www.sqlteam.com/article/writing-clr-stored-procedures-in-charp-returning-data-part-2

After a lot of trial and error and some Googleing to get some more results I found what I needed. I used a few of the examples provided by tedbilly to come up with this example of how to pull data from a database, return it from a function.

The schema to perform this test (just made it up, syntax is probably wrong)

CREATE TABLE [dbo].[Person](
      [id] [int] IDENTITY(1,1) NOT NULL,
      [Name] [varchar](100) )

CREATE TABLE [dbo].[Address](
      [id] [int] IDENTITY(1,1) NOT NULL,
      [PersonId] [int] NOT NULL,
      [Address] [varchar](100) )

The VB code

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Runtime.InteropServices

Partial Public Class UserDefinedFunctions

    <SqlFunction(FillRowMethodName:="FillRow", systemdataaccess:=SystemDataAccessKind.Read, DataAccess:=DataAccessKind.Read)> _
    Public Shared Function InitMethod(ByVal WhereClause As String) As IEnumerable

        Dim outDT As New DataTable

        Using adp As New SqlDataAdapter("Select * from Person where " & WhereClause, New SqlClient.SqlConnection("Context Connection=true"))
            adp.Fill(outDT)
        End Using

        Return outDT.Rows
    End Function

    Public Shared Sub FillRow(ByVal obj As Object, <Out()> ByRef Id As SqlInt32, <Out()> ByRef Name As SqlString, <Out()> ByRef Age As SqlInt32)
        Dim dr As DataRow = DirectCast(obj, DataRow)
        Id = New SqlInt32(CInt(dr.Item("Id")))
        Name = New SqlString(CStr(dr.Item("Name")))
        Age = New SqlInt32(CInt(dr.Item("Age")))
    End Sub


End Class

How to install it to the server after you added the assembly to the database:

CREATE FUNCTION GetUserNamesWithIdGreater(@WhereClause nvarchar(1000))
RETURNS TABLE (id int, [Name] nvarchar(100), Age int)
EXTERNAL NAME AssemblyName.[Namespace.UserDefinedFunctions].InitMethod;


Now comes the fun part! You can do this:

select * from dbo.GetUserNamesWithIdGreater('Age > 2') Person inner join Address on Person.Id = Address.PersonId

Really quick dynamic SQL which you can join on! Though I realize its not the best solution it is a fast change to an existing app which already uses a function.