If you want know how to create a SQL CLR project in Visual studio
look at this post
https://sqljunkieshare.com/2012/03/18/implementing-sql-clr-objects-in-sql-server/
lets create a scalar function
What is_deterministic , Userdataaccess, Systemdataaccess please use below links
https://sqljunkieshare.com/2012/02/13/user-defined-functions-sql-server/
Property Explanation
IsDeterministic : True if the function will always return the same value, if called with the same parameters. False if the function can return different values when called with the same parameters. An example of a deterministic function is the SayHello function. An example of a nondeterministic function is a function that will use database data that varies.
DataAccess Specifies whether the function will read data from the context SQL Server connection. Acceptable valuesare DataAccessKind.None and DataAccessKind.Read.
SystemDataAccess Specifies whether the function will require access to system catalogs or system tables in SQL Server—for
example, sys.object. Acceptable values are System DataAccessKind.None and SystemDataAccessKind.Read.
IsPrecise Specifies whether the function will be performing imprecise computation, for example, float operations.True if the function does not involve these
operations, false if it does. SQL Server uses this information for indexing.
FillRowMethodName Used for table-valued functions. This is the name of a method in the same class that serves as the
table-valued function contract.
TableDefinition Used for table-valued functions. Defines the column list in the table returned.
Name The name of the SQL function.
Use the .DLL and register the assembly in sql server and create a function
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(DataAccess= DataAccessKind.None,
IsDeterministic= true,SystemDataAccess=SystemDataAccessKind.None,IsPrecise=true)]
public static SqlInt32 ScalarUdf(SqlInt32 x, SqlInt32 y)
{
SqlInt32 z = x + y;
return (z);
}
};
USE [test]
GO
CREATE ASSEMBLY [CLR function]
AUTHORIZATION [dbo]
FROM ‘C:\Users\akhil\Documents\Visual Studio 2010\Projects\SQL CLR\CLR function\bin\Debug\CLR function.dll’
WITH PERMISSION_SET = SAFE
GO
create function sumxy (@x int, @y int)
returns int
as
external name [CLR function].UserDefinedFunctions.ScalarUdf
select dbo.sumxy(1,10)
Sqljunkieshare 