Geek Answers Handbook

Building CLR C # inside SQL Server

  • Is it possible to make a big project in C # (many functions), then
  • Create a CLR assembly for this,
  • In SQL Server IN A STORED PROC, call the function that is in the assembly,

  • The table (which I will pass to the ASSEMBLY) is computed in other stored procedures ...

    • If so, what will be the steps?

I think something like this.

-- I have a stored proc that gets a table, let it be myStoredProcTable --FIST ENABLE DATA ACCESS EXEC sp_serveroption 'TheServerName', 'DATA ACCESS', TRUE --main.sql calls yStoredProcTable.sql and the calls functionAssembly SELECT * INTO #tmpTable FROM OPENQUERY(SERVERNAME, 'EXEC test.dbo.myStoredProcTable 1') -- pass the table to assembly -- how would i pass the table to assembly code?, Is this POSSIBLE? EXEC functionAssembly #tmpTable

------------------------------------------ change

Following @faester answer: - How could I use XML in the code, I suggested using the numberTOstring object, but I think the XML option is best here ...

Again, I really do it, even if it's not the best choice ...

+7
source share
3 answers

Yes, you can register assemblies, but this is rarely a good idea due to performance issues.

But if you do complex numerical calculations or similar operations on scalar values, this can give you more flexibility. But the problem remains that SQL is tentatively oriented to C #, so you will easily encounter inconsistencies.

You should also know that you can only import static elements in static classes.

But an example This is a class that intentionally does not have a namespace, since it seems impossible to import classes into a namespace.

  public static class Math { [Microsoft.SqlServer.Server.SqlFunction] public static int Add(int a, int b) { return a + b; } [Microsoft.SqlServer.Server.SqlProcedure] public static void Void(int a, int b) { } }

Some SQL is required to prepare the server, and you probably need to be an administrator.

  EXEC SP_CONFIGURE 'clr enabled', 1 GO RECONFIGURE GO -- CONSIDER: DROP ASSEMBLY SqlClr GO CREATE ASSEMBLY SqlClr FROM 'pathtoassembly' WITH PERMISSION_SET = SAFE; GO SELECT * FROM sys.assemblies GO CREATE FUNCTION [MathAdd] (@a int, @b int) RETURNS INT AS EXTERNAL NAME [SqlClr].Math.[Add] GO CREATE PROCEDURE [Void] @a INT, @b INT AS EXTERNAL NAME [SqlClr].Math.[Void] GO SELECT dbo.MathAdd (1, 2) EXEC void 1, 2

AGAIN: You really have to be sure that you need it, this is rarely a good idea! (I used it once for email authentication, creating dns requests, etc., but it was on a system where all the business logic was written in SQL. And this is bad!)

Some useful links:

http://msdn.microsoft.com/en-us/library/ms189524.aspx

http://www.codeproject.com/KB/database/CLR_in_Sql_Server_2005.aspx

+5
source

Use the SQL CLR to perform the exact function that you described.

+1
source

Possibly . (But read the comments).

0
source

More articles:


All Articles