Implementing CLR User defined Scalar Functions in SQL server

If you want know how to create a SQL CLR project in Visual studio

look at this post

http://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

http://sqljunkieshare.com/2012/02/14/how-to-find-if-a-udf-is-deterministic-function-or-non-deterministic-function/

http://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)

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s