Good mornig friends!

Again I am having a trouble and I do not know what am I doing wrong when I try to retrieve a SQL parameter outside to .Net Clr

I did a clr example like this:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;
using Microsoft.SqlServer.Server;

namespace Test
{
    public class StoredProcedures
    {
    [SqlProcedure]
    public static void Test
    (
        SqlString p_t
    )
    {
      SqlCommand CommandTest = new SqlCommand();
      SqlParameter ParameterTest = new SqlParameter("@p_it", SqlDbType.NVarChar);
      StringBuilder sb = new StringBuilder();
      //String p_it; //Uncomment this line
      CommandTest.CommandType = CommandType.Text;
      CommandTest.Parameters.Add(ParameterTest);
      sb.AppendLine("SELECT");
      sb.Append(" @p_it = N'");
      sb.Append(p_t);
      sb.Append("';");
      CommandTest.CommandText = sb.ToString();
      using (SqlConnection conn = new SqlConnection("context connection=true"))
      {
        CommandTest.Connection = conn;
        conn.Open();
        SqlContext.Pipe.Send(sb.ToString());
        CommandTest.ExecuteNonQuery();
        SqlContext.Pipe.Send((String)CommandTest.Parameters["@p_it"].Value); //Comment this line
        //p_it = CommandTest.Parameters["@p_it"].Value.ToString(); //Uncomment this line
        //SqlContext.Pipe.Send(p_it); //Uncomment this line
      }
    }
  }
}

I now my trouble and it is because the parameter @p_it is null when I try to sed it to pipe in SqlContex but really it is not null.

When I test the clr with this code:

CREATE ASSEMBLY
 [Test]
FROM
 N'Z:\Some\Route\Test.dll'
WITH PERMISSION_SET = SAFE
GO

CREATE PROCEDURE [dbo].[Test]
(
  @p_e1 AS [SYSNAME]
)
AS EXTERNAL NAME [Test].[Test.StoredProcedures].[Test]
GO

EXECUTE [dbo].[Test]
 @p_e1 = N'Test'
GO

--DROP PROCEDURE [dbo].[Test]
--GO

--DROP ASSEMBLY [Test]
--GO

I obtain the next error message:

"Mens 6522, Nivel 16, Estado 1, Procedimiento Test, Lnea 14
A .NET Framework error occurred during execution of user-defined routine or aggregate "Test":
System.ArgumentNullException: El valor no puede ser nulo. -----> The value can not be null
Nombre del parmetro: message
System.ArgumentNullException:
   en Microsoft.SqlServer.Server.SqlPipe.Send(String message)
   en Test.StoredProcedures.Test(SqlString p_t)"

May any body help me telling me what I am doing wrong?

Thanks a lot for your valuable help.

Re

Good morning friends!

I solved the trouble but I do not know why this happens. I just add the next lines:

      ParameterTest.Direction = ParameterDirection.Output;
      ParameterTest.Value = "";

And the final code is:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;
using Microsoft.SqlServer.Server;

namespace Test
{
    public class StoredProcedures
    {
    [SqlProcedure]
    public static void Test
    (
        SqlString p_t
    )
    {
      SqlCommand CommandTest = new SqlCommand();
      SqlParameter ParameterTest = new SqlParameter("@p_it", SqlDbType.NVarChar);
      StringBuilder sb = new StringBuilder();
      ParameterTest.Direction = ParameterDirection.Output;
      ParameterTest.Value = "";
      CommandTest.CommandType = CommandType.Text;
      CommandTest.Parameters.Add(ParameterTest);
      sb.AppendLine("SELECT");
      sb.Append(" @p_it = N'");
      sb.Append(p_t);
      sb.Append("';");
      CommandTest.CommandText = sb.ToString();
      using (SqlConnection conn = new SqlConnection("context connection=true"))
      {
        CommandTest.Connection = conn;
        conn.Open();
        SqlContext.Pipe.Send(sb.ToString());
        CommandTest.ExecuteNonQuery();
        SqlContext.Pipe.Send(CommandTest.Parameters["@p_it"].Value.ToString());
      }
    }
  }
}

Thanks everybody!

Regards,

If you don't set the parameter as output, you will not get back the value you assigned to the T-SQL variable.

Thanks for your reply!

Regards,