Hi all,
This is a thorny one I've been struggling with for two days. I have some ADO.NET code that executes a sproc which loads a DataReader and evaluates the sproc's RETURN value, or at least it should. I'll post the sproc in a second, but if you look at it, there is a SELECT statement (commented out) right before the RETURN statement. The procedure works fine in SQL Server Express 2005 even uncommented. However, when the procedure is called from my ADO.NET code, the RETURN value is NULL if I uncomment the SELECT statement. With the SELECT statement commented out, the ADO.NET code gets the correct RETURN value. I can't figure out why??? Any thoughts much appreciated.
Here's the ADO.NET:
Code:
public static void TestReturn(string connectionString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "TestReturn";
command.Parameters.Add("@pageCount", SqlDbType.Int);
command.Parameters["@pageCount"].Direction = ParameterDirection.ReturnValue;
command.Parameters.Add("@pageNum", SqlDbType.Int);
command.Parameters["@pageNum"].Value = 1;
command.Parameters.Add("@pageSize", SqlDbType.Int);
command.Parameters["@pageSize"].Value = 5;
using (SqlDataReader reader = command.ExecuteReader())
{
// RETURN value is null with SELECT statement uncommented.
int returnValue = (int)command.Parameters["@pageCount"].Value;
if (reader.HasRows)
{
while (reader.Read())
{
Debug.WriteLine(reader["id"].ToString());
Debug.WriteLine(reader["itemdate"].ToString());
Debug.WriteLine(reader["title"].ToString());
}
}
}
}
}
}
Here's the sproc:
Code:
ALTER PROCEDURE [dbo].[TestReturn]
(
@pageNum int,
@pageSize int
)
AS
DECLARE @rows INT
DECLARE @keydate DATETIME
DECLARE @keyid INT
DECLARE @rowCount FLOAT
IF @pageNum = 1
BEGIN
SET @keydate= 0
SET @keyid=0
END
ELSE
BEGIN
/* get the values for the date and row */
SELECT @rows = (@pageNum-1) * @pageSize
SET ROWCOUNT @rows
SELECT @keydate=itemdate, @keyid=id FROM announcements ORDER BY itemdate ASC, id ASC
END
SELECT @rowCount=COUNT(*) FROM announcements
SET ROWCOUNT @pageSize
/*BEGIN
SELECT id, itemdate, title FROM Announcements
WHERE (itemdate > @keydate OR
(itemdate = @keydate) AND (id > @keyid))
ORDER BY itemdate ASC, id ASC
END*/
RETURN CEILING(@rowCount/@pageSize)
SELECT statement nullifying RETURN value
