You may find yourself with the need to integrate a .NET method within SQL Server to be called as a function. This usually happens when some relatively complex looping and modifying logic is a requirement of a SQL operation.
SQL is a great data language but it is not the right language for some tasks. Creating a SQL CLR from a .NET assembly may be the best approach to some unique situations (and there is a bonus in that, in many cases you can reuse existing .NET code).
Before creating the CLR object we need a .NET .dll; so first we create a basic .NET assembly compile in Release and copy the path the the compiled .dll:
Import into SQL Server instance via SSMS*:
...and then enter the path to your Release .dll
Create T-SQL function or stored procedure to serve as caller for the function and run it:
And that is all there is to it. Only use CLR functions when absolutely necessary as RDBMS's like SQL Server are designed to processes relational data in sets, and not to apply complex business logic on individual rows.
But if there is no other way- SQL CLRs could provide you a solution to your code/logic integration problems.
*Warning and Reference: https://blog.netspi.com/attacking-sql-server-clr-assemblies/
SQL is a great data language but it is not the right language for some tasks. Creating a SQL CLR from a .NET assembly may be the best approach to some unique situations (and there is a bonus in that, in many cases you can reuse existing .NET code).
Before creating the CLR object we need a .NET .dll; so first we create a basic .NET assembly compile in Release and copy the path the the compiled .dll:
This is our simple .NET CLR method with which we want to run within the SQL Server query execution engine
SQL CLR provides a way for you to integrate complex .NET methods within SQL Server
Import into SQL Server instance via SSMS*:
Select New Assembly...
Create T-SQL function or stored procedure to serve as caller for the function and run it:
From here we can see all of the T-SQL code involved; the 3 SQL Server configuration conditions (shown in the 3 EXEC statements) are required
And that is all there is to it. Only use CLR functions when absolutely necessary as RDBMS's like SQL Server are designed to processes relational data in sets, and not to apply complex business logic on individual rows.
But if there is no other way- SQL CLRs could provide you a solution to your code/logic integration problems.
*Warning and Reference: https://blog.netspi.com/attacking-sql-server-clr-assemblies/
No comments:
Post a Comment