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

  • The table (which I would pass to ASSEMBLY) is computed in other stored proc...

    • If so, What would be the steps?

I am thinking 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

------------------------------------------edit

Following @faester answer: - How could I use XML in the code, I suggested to use the numberTOstring thing, but I guess XML option is the best here...

Again, I really do this, even if is not the best choice...

Improve this question
3
  • Well, I'd like to, but those steps are already done. I thought this so I can make a bridge for all this code Commented Jun 15, 2011 at 20:52
  • 1
    Your design is the red carpet into thedailywtf.com Commented Jun 15, 2011 at 22:33
  • You know... the real world problems are very crazy... Commented Jun 16, 2011 at 2:47

3 Answers 3

Reset to default
6

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

But if you make complex numeric calculations or similar operations on scalar values it can give you a lot of flexibility. But the problem remains that SQL is natively set oriented which C# isn't, so you will easily run into mismatches.

You should also be aware that you can only import static members on static classes.

But an example This class - which intentionally doesn't have a namespace since it seems to be impossible to import classes in 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)
        {
        }
    }

It takes some SQL to get the server ready and you probably need to be admin. 

    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 should be confident that you need this, it is rarely a good idea! (I have used it once for email validation making dns lookups etc, but that was on a system where all business logics was written in SQL. And that is bad!)

Some useful references:

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

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

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Yes exactly what I need!!!!, only one thing , as you pass 2 ints as parameters I was thinking to pass column by column and assignt it to a var, so for example lets say if I have 10 columns, make 10 variables, and then assign to each var each column How would you pass or save in a variable a table column, I mean, as you said (@a int,@b int)... How to make a vector?
I am not sure, but in general there are mappings between CLR and SQL types, so perhaps you can pass in a DataTable that would translate into a construct like DECLARE @t TABLE (id int not null). This is pure guessing though. Your procedure could also read from some table.
Type conversion here: msdn.microsoft.com/en-us/library/ms131092.aspx - It looks as if you cannot input collections, but as I mentioned earlier anything but scalar values is expensive in this context. Your best option for inputting collections are probably to create SqlXml elements first.
I was thinking in doing a select table of a column and make the numbers a string declare @List varchar(max) select @List = isnull(@List + ',', '') + cast(ColumnName as varchar) from MyTable is this factible?, then in C# code I would restore the string to a vector..., could you please post some code following your XML idea....
About XML I was just thinking that is was simpler to pass multiple values in an XML doc than in some csv-style string. I have not used it so I cannot produce any code.
|
1

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

Improve this answer

1 Comment

Is it possible to make a dll with that c# code and call it in SQL?
0

It is possible. (But read the comments).

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.