Getting the output parameter after executing your stored procedure.

If you have a stored procedure that has an OUTPUT parameter it, it requires different syntax to execute and check the output parameter when you are using Query Analyzer or SQL Management Studio. Let's assume you have a very simple stored procedure that select 5 rows and just returns the number of rows in a table as an output parameter. create PROCEDURE [dbo].GetRows @RowCount int OUTPUT AS select top 5 * from Person select @RowCount = count(1) from Person GO Here is the syntax to test it from Query Analyzer or SQL Management Studio. Declare @TotalCount int exec GetRows @TotalCount OUTPUT select @TotalCount If you want to do this from ADO.Net, here is the syntax. Once again, there is a trick. You must close the reader before checking the value of the output parameter. In this example we are using a SQL Helper class similar to one of the early MS DAAB and custom objects, but this is not the important piece of code. The important thing here is that when using a reader, you must close the reader before accessing the output parameter. If you use a DataAdapter to load results into a heavy like DataTable or DataSet. public List<PersonEntity> FetchRows(out int rowCount) { SqlDataReader reader = null; List<PersonEntity> entities = new List<PersonEntity>();

try { _sqlServer = new SqlServer(_sqlConnStr);

SqlParameter[] parameters = new SqlParameter[1]; parameters[0] = _sqlServer.MakeOutParam("@RowCount", SqlDbType.Int, -1);

reader = _sqlServer.ExecuteReader("GetRows", parameters);

while (reader.Read()) { entities.Add(FillEntityFromReader(reader)); }

// we must close the reader before we can read an output parameter reader.Close(); rowCount= (int)parameters[3].Value;

} catch (Exception ex) { throw ex; } finally { // a little overkill, but just for clarity, close everything explicitly if (reader != null && !reader.IsClosed) { reader.Close(); }

if (_sqlServer != null) { _sqlServer.Close(); _sqlServer.Dispose(); } }

return entities; } To call this method from C#, you would do something like: int count; FetchRows(out count); // use count here