Problem with stored procedures and output parameters
Posted: Wed 02 Mar 2011 10:01
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:
Here are few examples for stored procedure and command captured through profiler...
Expected behavior would be always like this:
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
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 @p1Environment:
MS Visual Studio 2010
Devart dotConnect for SQL Server 2.40.239.0
SQL Server 2008 R2