CODEWITHSUNDEEP
c#sql-serverado.net
Krzysztof Madej
Krzysztof Madej

Reputation: 40729

How to retrieve out parameter using text command

I need to find a way to retrieve out parameter for the following query:

query = @"declare @p7 int
          exec some_sproc @SomeArg=@p7
          select @p7";

sqlCommand.CommandText = query;
sqlCommand.CommandType = CommandType.Text;

I tired with adding SQL paramater but then the whole code was simply wrapped with exec sp_executesql and the parameter was added. This simply doesn't work. Do you know how I could get value of @p7 on C# side?

My stored procedure uses table valued parameters, however by default it is evaluated to 

declare @p11 acco.SomeUDT
insert into @p11 values(1,'2017-06-15 00:00:00',64.73)
insert into @p11 values(1,'2017-06-15 00:00:00',1.30)

But my intention is to get following syntax:

declare @p11 acco.SomeUDT
insert into @p11 values(1,'2017-06-15 00:00:00',64.73), (1,'2017-06-15 00:00:00',1.30)

This is why I build manually SQL code. So we have:

query = @"declare @p7 int

          declare @p11 acco.SomeUDT
          insert into @p11 values(1,'2017-06-15 00:00:00',64.73), (1,'2017-06-15 00:00:00',1.30)

          exec some_sproc @SomeArg=@p7, @SomeUDTArg=@p11
          select @p7";
sqlCommand.CommandText = query;
sqlCommand.CommandType = CommandType.Text;

If I use stored procedure type and SQL parameters than I get first syntax.

All works but I need to map also out parameter @SomeArg and I have problem with this.

Upvotes: 2

Views: 2453

Answers (2)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131581

This stored procedure is using a table-valued parameter as input and fills an output parameter. There is no reason to fill the TVP in the script, you can fill it on the client side.

In fact, that was one of the main benefits of using TVPs. Constructing the TVP on the server side throws it away.

sqlCommand.CommandText = "some_sproc";
sqlCommand.CommandType = CommandType.StoredProcedure;

//Create the out parameter
var outParam=sqlCommand.Parameters.Add("@SomeArg", SqlDbType.Int);
outParam.Direction = ParameterDirection.Output;

//Set the TVP value
var tvpParam=sqlCommand.Parameters.Add("@SomeUDTArg",SqlDbType.Structured);
tvpParam.Value=myTable;

//Run it
sqlCommand.ExecuteNonQuery();


//And read the results
var result=outParam.Value;

myTable can be a strongly-typed DataTable or a DataTable created at runtime. I'm lazy so I'll use the ToDataTable extension method from the MoreLinq package to create a DataTable from a strongly-typed collection:

public class MyRecord 
{ 
    public int ID{get;set;}
    public DateTime Date {get;set;}
    public decimal Value {get;set;}
    public MyRecord(int id,DateTime date,decimal value){....}
}

...

var myValues=new List<MyRecord>
             {
                 new MyRecord(1,new DateTime(2017,6,15),64.73m),
                 new MyRecord(1,new DateTime(2017,6,15),1.39m)
             };

var myTable=myValues.ToDataTable();

If the field names of the UDT and property names the class match, no other change is necessary.

If you want to use a set of records as a default TVP value, you can create it once and store eg in a static or lazily-initialized field. This way, you can create a method that uses the default value if no other parameters are provided:

void DoSomething(IEnumerable<MyRecord> records)
{
    ...
    //Set the TVP value
    var tvpParam=sqlCommand.Parameters.Add("@SomeUDTArg",SqlDbType.Structured);  

    tvpParam.Value=(records==null)?DefaultValues:records.ToDataTable();

    //Run it
    sqlCommand.ExecuteNonQuery();

    ...

}

Upvotes: 1

GarethD
GarethD

Reputation: 69789

You should be able to do this by simply not declaring @p7 within the query, but instead adding it as a parameter to the SQL CommandText

query = @"declare @p11 acco.SomeUDT
          insert into @p11 
          values(1,'2017-06-15 00:00:00',64.73), (1,'2017-06-15 00:00:00',1.30)
          exec some_sproc @SomeArg=@p7, @SomeUDTArg=@p11;";

sqlCommand.CommandText = query;
sqlCommand.CommandType = CommandType.Text;
sqlCommand.Parameters.Add("@p7", SqlDbType.Int).Direction = ParameterDirection.Output;
sqlCommand.ExecuteNonQuery();

Console.WriteLine(sqlCommand.Parameters["@p7"].Value);

Also, If it is an output parameter in your procedure then you probably should be using:

exec some_sproc @SomeArg=@p7 OUT, @SomeUDTArg=@p11; 
                             ^^^

EDIT

If you have any concerns regarding Bobby tables, conversion problems or all the reasons why it's a bad idea to construct SQL strings like this, you could create the table in c# and pass it as a parameter too:

var table = new DataTable();
// TODO: Add the correct column names for your TVP here
table.Columns.Add("Column1", typeof(int));
table.Columns.Add("Column2", typeof(DateTime));
table.Columns.Add("Column3", typeof(decimal));

table.Rows.Add(1, new DateTime(2017, 6, 15), 64.73);
table.Rows.Add(1, new DateTime(2017, 6, 15), 1.3);

sqlCommand.CommandText = "some_sproc";
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Parameters.Add("@SomeArg", SqlDbType.Int).Direction = ParameterDirection.Output;

var tvp = sqlCommand.Parameters.Add("@SomeUDTArg", SqlDbType.Structured);
tvp.TypeName = "acco.SomeUDT";
tvp.Value = table;

sqlCommand.ExecuteNonQuery();

Console.WriteLine(sqlCommand.Parameters["@SomeArg"].Value);

Upvotes: 1

Related Questions