CODEWITHSUNDEEP
sqlsql-servert-sqlsqlclr
PanosPlat
PanosPlat

Reputation: 958

Getting results from SQLCLR

I have the following issue: I must have a DLL accessed from various clients inside the LAN, that resides in a the PC that hosts the SQL Server.

I need to access this DLL from all the clients in order to have it return a legal-tax checksum.

I thought to create a CLR procedure that calls the DLL in the server and returns the checksum.

I followed some internet examples and created a simple HelloWorld app....

I created a DLL in C#

[SqlProcedure]
public static int HelloWorld(int h)
{
    return h;
}

I created the assembly in SQL Server:

CREATE ASSEMBLY MyChecksum
    from 'C:\Users\Panos\Documents\Visual Studio 2010\Projects\DigitalSignature\Algobox\bin\Debug\checksum.dll'
    WITH PERMISSION_SET = UNSAFE

I created the procedure

CREATE PROC sp_mychecksum (@h int)
AS
EXTERNAL NAME MyAlgobox.Signatures.HelloWorld

The bottom line is that when I execute my procedure (exec sp_mychecksum
12) I get only a message

Command(s) completed successfully

and not the 12

Any clues?

Upvotes: 0

Views: 311

Answers (1)

Clark
Clark

Reputation: 376

The way you've defined things, the return value from the HelloWorld() method is being returned as the return value of the stored procedure. So, as suggested by @Damien_The_Unbeliever in the comments of your question, this will get you what you're looking for on the SQL Server:

DECLARE @rc int
EXEC @rc = sp_mychecksum 12
SELECT @rc

To have your HelloWorld() method return the value as a resultset, you would have to do something like this (warning, untested):

[SqlProcedure]
public static int HelloWorld(int h) {
    SqlDataRecord record = new SqlDataRecord(new SqlMetaData("colname", SqlDbType.Int));
    record.SetSqlInt32(0, h);
    SqlContext.Pipe.Send(record);
}

However, given your use case, I would think you want to map your existing CLR routine to a SQL function, not a stored procedure. For example, something like this:

CREATE FUNCTION dbo.f_mychecksum(@h int) RETURNS int
WITH EXECUTE AS CALLER 
AS
EXTERNAL NAME MyAlgobox.Signatures.HelloWorld

And then use it like this:

SELECT dbo.f_mychecksum(12)

Upvotes: 1

Related Questions