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...
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*/
Code: Select all
declare @p1 int
set @p1=42
exec test_proc @TestPar=@p1 output
select @p1
Environment:
MS Visual Studio 2010
Devart dotConnect for SQL Server 2.40.239.0
SQL Server 2008 R2