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.

  • Change entity map to another “unknown” table at runtime
  • SQL Server query joining a table with the results of another query
  • Sql query, selecting on unique identifier gives - Error converting data type varchar to uniqueidentifier
  • How bad is opening and closing a SQL connection for several times? What is the exact effect?
  • Cannot insert the value NULL into column 'Id' (Database first)
  • SQL Server big amout of data in one or more tables
  • 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.