Hi all,

In a recent conversation with Don, I realized that I had working SQLCLR code with an F# assembly that others may find useful.

I'll publish this as an article in the coming days. The sample will include a scalar-valued function, a table-valued function, an aggregate function and some other SQLCLR goodness. There is no ETA on my article as my primary focus is the June San Diego Code Camp Presentations first and foremost.

To run this sample:

1. Get the attached file fSQLSample.zip.
2. Unzip it (obviously).
3. Restore the fsharp1 database.
4. Open the fSQLTests solution in VS2005.
5. Open fSQLSetup.txt file.
6. Copy the final SELECT statement to the clipboard.
7. Open the restored fsharp1 SQL database in SQL Management Console
8. Navigate to fsharp1, and create a new view.
9. Paste the SELECT statement into the view and execute it.

The results of the query will be the projection of the 2opTable columns operand1, operand2, the sum of operand1 and operand2 as calculated using SQL and the '+' operator and finally, a call to fSQLSum with operand1 and operand2. Note that the third and fourth columns should be the sum of the first two columns (and should match each other if it worked).

Note that I've included two F# projects. The TVF project is only a shell and not functional. The SVF project is about as simple as it can get; one file with a module definition and a function definition.

1
2

module addTest
let fSQLSum x y = x + y

Ignore that final let aa = 44 statement in the file as that was just a placeholder and does nothing.

Here's the SQL:

1
2

-- Note non-conformist tendencies in table name
SELECT operand1, operand2, operand1 + operand2, fSQLSum(operand1, operand2) from dbo.[2opTable]

Note that I had to use the --standalone flag to build the project so that the F# assembly had necessary stuff when living inside of SQL 2005. Also note that if you'd like to reproduce this sample in another database, I've included the SQL in fSQLSetup.txt. The Trustworthy setting or an alternative (more on this at another time) MUST be used for any database that houses F# assemblies.

I'd prefer if this didn't go out in the wild until I've been able to do more testing of the SQLCLR and F#. There is some risk at the moment in that F# does use static variables and SQLCLR disallows those (hence the unsafe registration of the assembly).

This has been tested against SQL2005 and F# 1.1.10.4 (and other versions).

Regards,

---O