Calling the DLL from SQL2008.Returning Results from CLR Stored Procedures

Post date: Apr 12, 2011 1:26:26 PM

As with Transact-SQL stored procedures, information may be returned from .NET Framework stored procedures using OUTPUT parameters. The Transact-SQL DML syntax used for creating .NET Framework stored procedures is the same as that used for creating stored procedures written in Transact-SQL. The corresponding parameter in the implementation code in the .NET Framework class should use a pass-by-reference parameter as the argument. Note that Visual Basic does not support output parameters in the same way that Visual C# does. You must specify the parameter by reference and apply the <Out()> attribute to represent an OUTPUT parameter, as in the following:

using System; using System.Data.SqlTypes; using System.Data.SqlClient; using Microsoft.SqlServer.Server;   public class StoredProcedures  {    [Microsoft.SqlServer.Server.SqlProcedure]    public static void PriceSum(out SqlInt32 value)    {       using(SqlConnection connection = new SqlConnection("context connection=true"))        {          value = 0;          connection.Open();          SqlCommand command = new SqlCommand("SELECT Price FROM Products", connection);          SqlDataReader reader = command.ExecuteReader();                    using (reader)          {             while( reader.Read() )             {                value += reader.GetSqlInt32(0);             }          }                }    } }

Once the assembly containing the above CLR stored procedure has been built and created on the server, the following Transact-SQL is used to create the procedure in the database, and specifies sum as an OUTPUT parameter.

CREATE PROCEDURE PriceSum (@sum int OUTPUT) AS EXTERNAL NAME TestStoredProc.StoredProcedures.PriceSum

Note that sum is declared as an int SQL Server data type, and that the value parameter defined in the CLR stored procedure is specified as a SqlInt32 CLR data type. When a calling program executes the CLR stored procedure, SQL Server automatically converts the SqlInt32 CLR data type to an int SQL Server data type. For more information about which CLR data types can and cannot be converted, see Mapping CLR Parameter Data.

To create a dynamic result set, populate it and send it to the client, you can create records from the current connection and send them using SqlPipe.Send.

using System.Data; using System.Data.SqlClient; using Microsoft.SqlServer.Server;  using System.Data.SqlTypes;  public class StoredProcedures  {    /// <summary>    /// Create a result set on the fly and send it to the client.    /// </summary>    [Microsoft.SqlServer.Server.SqlProcedure]    public static void SendTransientResultSet()    {       // Create a record object that represents an individual row, including it's 

metadata.       SqlDataRecord record = new SqlDataRecord(new SqlMetaData("stringcol", 

      SqlDbType.NVarChar, 128));              // Populate the record.       record.SetSqlString(0, "Hello World!");              // Send the record to the client.       SqlContext.Pipe.Send(record);    } }

Here is an example of sending a tabular result and a message through SqlPipe.

using System.Data.SqlClient; using Microsoft.SqlServer.Server;   public class StoredProcedures  {    [Microsoft.SqlServer.Server.SqlProcedure]    public static void HelloWorld()    {       SqlContext.Pipe.Send("Hello world! It's now " + 

        System.DateTime.Now.ToString()+"\n");       using(SqlConnection connection = new SqlConnection("context connection=true"))        {          connection.Open();          SqlCommand command = new SqlCommand("SELECT ProductNumber FROM ProductMaster", 

          connection);          SqlDataReader reader = command.ExecuteReader();          SqlContext.Pipe.Send(reader);       }    } } 

CREATE PROCEDURE HelloWorld() AS BEGIN PRINT('Hello world!') SELECT ProductNumber FROM ProductMaster END

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