Call dll function from sql stored procedure using current connection
Can a DLL be called from a stored procedure using an open connection?
I have a dll that receives data from SQL Server and I don't want to open a new connection when I call it from a stored procedure.
thank
Here's an example
public class Class1
{
public static SqlString GetName(SqlString str)
{
SqlCommand cmd = new SqlCommand(str.ToString());
cmd.CommandType = System.Data.CommandType.Text;
string name = cmd.ExecuteScalar().ToString();
return name;
}
}
and this is the SQL code
CREATE FUNCTION fn_TestConnection
(
@str nvarchar(255)
)
RETURNS nvarchar(max)
AS EXTERNAL NAME TestConnection.[TestConnection.Class1].GetName
GO
SELECT dbo.fn_TestConnection('SELECT FName FROM Clients WHERE Id = 1' )
0
source to share
1 answer
First of all you need to create an assembly in Sql server as
Your Database -> Programming -> Collections -> Create Assembly
Then you need to create a sql function to wrap your build method like this:
CREATE FUNCTION [dbo].[fn_funcName](@str [varchar](max))
RETURNS
varchar(max)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [YourSqlAssemblyName].[YourAssemblyName.Class1].[GetName]
If you want to return a table from your function, read about SqlFunctionAttribute in .NET.
+2
source to share