Join Martin Guidry for an in-depth discussion in this video Writing functions with .NET, part of SQL Server: Triggers Stored Procedures and Functions.
In this section I am going to talk about using C# to write a user defined function. So here in Visual Studio we'll create a new project. Again, I want it to be of type Class Library and I'll call it a CLRfunctions. I prepared some code ahead of time that you can find in your exercise files, so I'll copy and paste that over to Visual Studio and we can talk about it. The top eight lines are typical using statements that we have with any SQL Server stored procedure or function, and then I created a new class, starting on line 10, public class myFunctions. There is one method in there called isActive, it is decorated with Microsoft.Sql.Server .Server.Sql.Function. This will provide a clue to the compiler that we are in fact writing a SQL function.
Notice on line 13 we declared the method to be public static and the Return type is SqlString. When we're working with C# code, that's going to eventually be executed inside of SQL Server, we have to use slightly different data types, rather than use things like sting, an integer, we've to specify it as a SqlString or a SqlInteger. So we work with slightly different data types. Here we have SqlIntegar as the Return type of the function, and we're passing one parameter called ActiveBit and that is a SqlInt, in this case at 32bit SqlInteger, so SqlInt32.
The point of this function, it is going to read the bit, the 1 or 0 we used to store whether or not someone is active, and just return something that's a little more friendly, return some text that actually spells out the word Active or Not Active, based on the value of that bit. So we see some fairly straightforward logic; line 15, 16, 17 and 18, a simple if statement, if (ActiveBit == 1) we'll return the phrase Active, else, we return the phrase Not Active.
So this is all the code we need. We go ahead and Build Solution, and in the lower left we see the build succeeded. That's certainly good news. Now we'll need to save all of this in a place that's easy to find, and now that should be everything we need to do in Visual Studio. We will flip over to SQL Server Management Studio, we will need to import the New Assembly, and we'll have to browse to where we just dropped that. We successfully created the assembly CLRFunctions.
So now it's time to create our function. I have again pre-staged some code for you that's in your exercise files. We will copy and paste that. We see it says CREATE FUNCTION, we gave it a name, and then it's going to take one parameter. That parameter is an integer. We say it RETURNS NVARCHAR. So we talked earlier a little bit about the differences in data types, C# is going to return what it calls a SqlString. SQL recognizes is that as the NVARCHAR data type, and we'll have to match those up.
If those didn't match we would get an error when creating this function. And in the bottom part EXTERNAL NAME, which again goes assemblyname.classname.method, so that CLRfunctions.myFunctions.isActive. We execute that. It says Command(s) completed successfully. That's good news. I can come over here in my Functions section and expand out Scalar-valued Functions, hit a quick refresh, and I see isActive is now a valid function.
So now we're going to write a SELECT statement that actually uses that. So I want to SELECT FROM the Authors table. And I want to select three things: FirstName, LastName, and this new function we just created. That takes one input parameter and that parameter will be the ActiveBit.
We run that, and we see every line Returns Not Active, which is in fact accurate. All of our Authors are In Active, based on things we did in previous demos. This might be a little bit more interested if we had an author who was active, so let's go ahead and do that. We'll make John Doe, we'll make him Active, and we'll run this again. We see our function is operating properly. Anytime the Active field is equal to 1, our function writes the phrase Active, and anytime the Active field is not equal to 1, it writes the phrase Not Active.
- Comparing triggers, functions, and stored procedures
- Installing and configuring SQL Server
- Creating a stored procedure
- Returning data using data sets
- Creating user-defined functions
- Using "after," "instead," and nested triggers
- Modifying existing stored procedures
- Implementing logging on DELETE
- Choosing between T-SQL and CLR
- Executing a stored procedure
- Passing parameters