Call dll function from sql stored procedure using the current connection

Can I call a dll from a stored procedure using the open connection?

I have a dll that gets data from SQL Server and I don’t want to open a new connection when I call it from the stored procedure.

  • SQL select max(date) and corresponding value
  • Database integration tests in Visual Studio Online
  • ASP.NET MVC Deployed Application Server Address
  • Does TransactionScope Need DTC Service on?
  • SQL Server PIVOT - Multiple Aggregates
  • Drop User from SQL Server Database?
  • Thank you

    Here is an exemple

    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' )
    

    One Solution collect form web for “Call dll function from sql stored procedure using the current connection”

    First of all you need to create assembly in Sql Server as

    Your DataBase -> Programming -> Assembles -> Create Assembly

    And next you need to create sql function to wrap your assembly 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 table from your function read about SqlFunctionAttribute in .NET.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.