Problem with stored procedures and output parameters

[dbembic]

I am trying to do this:
I have stored procedure that is returning dataset and I need to send parameter with some value and get scalar result through that same parameter. I am getting some strange behavior from SqlCommand. I used SQL Server Profiler to find out what is happening on the server. Here is sample code:

Code: Select all

Devart.Data.SqlServer.SqlCommand command = new Devart.Data.SqlServer.SqlCommand();
command.Connection = some_connection;
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = "test_proc";
Devart.Data.SqlServer.SqlParameter par = new Devart.Data.SqlServer.SqlParameter("@TestPar", Devart.Data.SqlServer.SqlType.Int);
par.Direction = System.Data.ParameterDirection.InputOutput;
par.Value = 42;
command.Parameters.Add(par);
Devart.Data.SqlServer.SqlDataReader reader = command.ExecuteReader();
// do something with dataset, get @TestPar value...

Here are few examples for stored procedure and command captured through profiler...

Code: Select all

create procedure test_proc @TestPar int = NULL output
AS
SELECT 1

/* Profiler:
declare @p1 int
set @p1=42
exec test_proc @TestPar=@p1 output
select @p1*/

Code: Select all

create procedure test_proc @TestPar int = NULL output
AS
SET @TestPar = @TestPar + 1
SELECT 1

/* Profiler:
declare @p1 int
set @p1=43
exec test_proc @TestPar=@p1 output
select @p1*/

Code: Select all

create procedure test_proc @TestPar int = NULL output
AS
SET @TestPar = 22
SELECT 1

/* Profiler:
declare @p1 int
set @p1=22
exec test_proc @TestPar=@p1 output
select @p1*/

Expected behavior would be always like this:

Code: Select all

declare @p1 int
set @p1=42
exec test_proc @TestPar=@p1 output
select @p1

I know that there is a workaround. I can use procedure with 2 params, one for input and one for output, but I would like to avoid that. Is there something that I'm doing wrong?

Environment:
MS Visual Studio 2010
Devart dotConnect for SQL Server 2.40.239.0
SQL Server 2008 R2

[Shalex]

We have reproduced the mentioned behaviour. We will investigate it and notify you about the results as soon as possible.

[Shalex]

Unfortunately, we do not have a technical possibility to overcome this problem.

[HeatherHope]

How should I insert a row through button in stored procedures.
If you have any alternative then please tell me.
I am waiting for your reply.

[Shalex]

How should I insert a row through button in stored procedures.

Could you please explain what you mean? Our tutorials are available at http://www.devart.com/dotconnect/sqlser ... rials.html.