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
