Stored Procedure Problem

Question

I have a problem with accessing output in the stored procedure below

        DbParameter DbParm = command.CreateParameter();
        DbParm.Direction = ParameterDirection.Output;
        DbParm.ParameterName = "@SomeValue";
        DbParm.DbType = DbType.Int32;
        DbParm.Value = null;
        command.Parameters.Add(DbParm);

After executing the procedure

        command.Parameters["@SomeValue"].Value;

Well it always returns Null,I can access to the second second select though.Here is the procedure:

CREATE PROCEDURE SomeThing
  @SomeValue int OUTPUT,
AS

  SELECT @SomeValue=ID  
    FROM SomeTable
   WHERE ID=10;

  SELECT * 
    FROM SomeTable;

GO

Answer 1

EDIT: I think I see the problem now. SQL Server does not assign output parameters until the procedure has finished. While the second select is streaming, the output parameter will not be returned to the client.

Now I'm assuming you're calling ExecuteNonQuery, which does not ask for a result set. SQL Server will halt the procedure at the start of the second select.

If you change your C# code to use ExecuteReader(), you can retrieve the output parameter after the reader is done:

using (var read = yourCommand.ExecuteReader())
    while (read.Read());
// You should be able to access the output parameter here

If you add more streaming selects to the procedure:

SELECT * FROM SomeTable where id = 1;
SELECT 'hello world';
SELECT * FROM SomeTable where null in (1,2,3);

You have to get them all out of the way with NextResult:

using (var read = yourCommand.ExecuteReader()) {
    do {
         while (read.Read());
    } while (read.NextResult());
}

before the output parameter will be assigned. The reason for this is that the value of the output parameter can depend on the last select. So SQL Server doesn't know the right value before it finishes executing the entire stored procedure.

Answer 2

I've never used dbparameter objects, only system.data.sqlclient.sqlparameter objects to accomplish passing parameters to a stored procedure on SQL server. Try SqlParameter instead.