Tuesday, 22 November 2011
Using CLR functions with T-SQL
Why use CLR?
Before using CLR, you should question ‘Why’. When using CLR functions within T-SQL, you have to maintain two different programming environments, unless what you gain worth more than having it.
We need to use the CLR functions when occasions such as:
When we have to access system resources like file system or network (**Extended stored procedures can do the same. But they are deprecated and will be removed from future versions of SQL)
Or when the business logics are too complex to write in T-SQL, which can be easily done using .net languages (Reusing the logics already written, without duplicating them using T-SQL)
To illustrate this I will create a CLR function which will create a text file and append the text which is passed from a T-SQL statement.
Open Visual studio and create a new ‘SQL Server Project’
Next dialog you will be prompted to select the database which you plan to deploy your extension. You can either select from existing or connect to a different one.
In next screen you will be prompted, whether you want to debug your .net coding. Select ‘yes’ or ‘no’ depending on your requirement.
Right click on the newly created project and add new user-defined- function
I have named it as ‘WriteToTextFileSample’.
Use the following code:
**The functions should be 'public static' and all the functions should have '[Microsoft.SqlServer.Server.SqlFunction]' attribute or you will not be able to call it from SQL.
Go to the project properties and change the ‘Permission Level’ to ‘Unsafe’ in Database section. (This is only required if you are performing tasks such as writing to files, accessing system related resources. You do not need this if you are performing calculations, etc..)
Save your project. Before deploying this you have to enable the ‘clr enable’ to ‘1’. Use the following T-SQL statement to do so.
Once executed, deploy the project (Build—> Deploy Solution). You will be prompted with the SQL credentials.
Once it’s deployed successfully use the following sample statement to use the function which we have created.