Problem with sproc

Bob Bedell · January 18th, 2008, 02:25 PM

Hello,

I'm having a problem running a sproc in SQL Server 2005 Express from a C# console application (.NET 2.0).

The sproc returns a value that the ADO.NET code in the console app needs. it also returns a SELECT result set though, and thats where the problems start. Here is the sproc:

Code:

ALTER PROCEDURE [dbo].[Test]
(
    @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)

Bob Bedell · January 18th, 2008, 02:34 PM

OOOOOOPsss...

Meant to preview and posted prematurely...Here's the rest of my post:

The stored procedure above works fine. The return value is picked up by my ADO.NET code. However, is I uncomment the SELECT statement at the bottom, RETURN is NULL. Its like running the SELECT statement cancels the RETURN value.

Here is the ADO.NET code:

Code:

public static void Test4() {
            string connectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=NorthwindTables;User Id=sa;Password=thomasse";

            using (SqlConnection connection = new SqlConnection(connectionString)) {

                connection.Open();
                using (SqlCommand command = connection.CreateCommand()) {
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = "Test";

                    command.Parameters.Add("@pageNum", SqlDbType.Int);
                    command.Parameters.Add("@pageSize", SqlDbType.Int);
                    command.Parameters["@pageNum"].Value = 1;
                    command.Parameters["@pageSize"].Value = 5;

                    command.Parameters.Add("@pageCount", SqlDbType.Int);
                    command.Parameters["@pageCount"].Direction = ParameterDirection.ReturnValue;


                    using (SqlDataReader reader = command.ExecuteReader()) {

                        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());
                            }
                        }
                    }
                }
            }

If I uncomment the last SELECT statement, the DataReader is populated with the result set, but the RETURN value is lost ( = NULL).

Any thoughts?

Bob

planoie · January 18th, 2008, 04:24 PM

Bob,

Although I can't provide a justification, I can kind of see why this appears as acceptable behavior. By doing a SELECT in a SPROC you are returning data. Just not in the RETURN statement.

If you need to select records and provide a scalar return value you might be better off to use an output parameter instead. Set the parameter value before you run the select.

-Peter

Bob Bedell · January 18th, 2008, 07:27 PM

Hi Peter,

Thanks for the replys, and I apologize for the cross-posting, but there is something sinister at work here. I have tried every output parameter and return value permutation imaginable over the past two days. The whole thing feels buggy to me. Here is a sproc taken directly out of Sql Sever 2005 Books Online that includes both an output parameter and a return value (it hits MS's AdventureWorks db, the successor sample db to Northwind). It selects from a view, makes an output parameter assignment, returns 0 if all went well:

Code:

-- Create a procedure that takes one input parameter
-- and returns one output parameter and a return code.
CREATE PROCEDURE SampleProcedure @EmployeeIDParm INT,
         @MaxTotal INT OUTPUT
AS
-- Declare and initialize a variable to hold @@ERROR.
DECLARE @ErrorSave INT
SET @ErrorSave = 0

-- Do a SELECT using the input parameter.
SELECT FirstName, LastName, JobTitle
FROM HumanResources.vEmployee
WHERE EmployeeID = @EmployeeIDParm

-- Save any nonzero @@ERROR value.
IF (@@ERROR <> 0)
   SET @ErrorSave = @@ERROR

-- Set a value in the output parameter.
SELECT @MaxTotal = MAX(TotalDue)
FROM Sales.SalesOrderHeader;

IF (@@ERROR <> 0)
   SET @ErrorSave = @@ERROR

-- Returns 0 if neither SELECT statement had
-- an error; otherwise, returns the last error.
RETURN @ErrorSave
GO

Here's an ADO.NET procedure I wrote to execute it:

Code:

 public static void test_OutputParam(string connectionString) 
        {
            using (SqlConnection connection = new SqlConnection(connectionString)) {

                connection.Open();
                using (SqlCommand command = connection.CreateCommand()) {
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = "test_OutputParam";

                    command.Parameters.Add("@EmployeeIDParm", SqlDbType.Int);
                    command.Parameters["@EmployeeIDParm"].Value = 1;

                    command.Parameters.Add("@MaxTotal", SqlDbType.Int);
                    command.Parameters["@MaxTotal"].Direction = ParameterDirection.Output;

                    command.Parameters.Add("@RetVal", SqlDbType.Int);
                    command.Parameters["@RetVal"].Direction = ParameterDirection.ReturnValue;

                    using (SqlDataReader reader = command.ExecuteReader()) {

                        // STILL NULL!?!?!?!?!
                        int outputValue = (int)command.Parameters["@MaxTotal"].Value;
                        int returnValue = (int)command.Parameters["@RetVal"].Value;

                        if (reader.HasRows) 
                        {

                            while (reader.Read()) 
                            {
                                Debug.WriteLine(reader["FirstName"].ToString());
                                Debug.WriteLine(reader["LastName"].ToString());
                                Debug.WriteLine(reader["JobTitle"].ToString());
                            }
                        }
                    }
                }
            } 
        }

The output parameter and return parameter are still null. You don't by any chance have AdventureWorks installed on SQL Server Express 2005 do you, and could give this a whirl on your box. Or I'd be happy to kick out a Northwind version. It simply doesn't work for me. I'm stumped.

Best,

Bob

Bob Bedell · January 18th, 2008, 09:02 PM

Quote:

quote:By doing a SELECT in a SPROC you are returning data. Just not in the RETURN statement

I think this must be right Peter. I offcially don't think there is a way to get ADO.NET to retrieve BOTH a multiple result set AND either a return value or output parameter from a sproc. I think ADO.NET limits you to a scalar result set if you also want a return value or output parameter.

What sent me barking up the wrong tree is the behavior of the SqlDataSource object. For example, the following configuration enables a SqlDataSource object to use both the return value and the multiple result set of the first sproc I posted above:

Code:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:TestDB %>"
     SelectCommand="PagedAnnouncementList" SelectCommandType="StoredProcedure" OnSelected="SqlDataSource1_Selected">
     <SelectParameters>
          <asp:ControlParameter Name="pageNum" ControlID="pn1" PropertyName="SelectedPage" />
          <asp:Parameter DefaultValue="10" Name="pageSize" Type="Int32" />
          <asp:Parameter Name="pageCount" Direction="ReturnValue" Type="Int32" />
     </SelectParameters>
</asp:SqlDataSource>

Because a SqlDataSource object can do it, I assumed ADO.NET could do it too. I think I'm wrong.

In order to execute the second sproc I posted above using ADO.NET in a way that gets both the return value and a scalar "resultset", I think you'd need to loose the DataReader and rewrite the sproc using multiple output parameters. Like this:

Code:

ALTER PROCEDURE [dbo].[test_OutputParamNonQuery] 
        @EmployeeIDParm INT,
        @FirstName VARCHAR(40) OUTPUT, 
        @LastName VARCHAR(40) OUTPUT, 
        @JobTitle VARCHAR(40) OUTPUT,
        @MaxTotal INT OUTPUT
AS
-- Declare and initialize a variable to hold @@ERROR.
DECLARE @ErrorSave INT
SET @ErrorSave = 0

-- Do a SELECT using the input parameter.
SELECT @FirstName = FirstName, 
       @LastName = LastName, 
       @JobTitle = JobTitle
FROM HumanResources.vEmployee
WHERE EmployeeID = @EmployeeIDParm

-- Save any nonzero @@ERROR value.
IF (@@ERROR <> 0)
   SET @ErrorSave = @@ERROR

-- Set a value in the output parameter.
SELECT @MaxTotal = MAX(TotalDue)
FROM Sales.SalesOrderHeader;

IF (@@ERROR <> 0)
   SET @ErrorSave = @@ERROR

-- Returns 0 if neither SELECT statement had
-- an error; otherwise, returns the last error.
RETURN @ErrorSave

Then in ADO.NET, you'd need to use the ExecuteNonQuery method of the Command object:

Code:

public static void test_ExecuteNonQuery(string connectionString) 
        {
            using (SqlConnection connection = new SqlConnection(connectionString)) 
            {
                connection.Open();
                using (SqlCommand command = connection.CreateCommand()) 
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = "test_OutputParamNonQuery";

                    command.Parameters.Add("@RetVal", SqlDbType.Int);
                    command.Parameters["@RetVal"].Direction = ParameterDirection.ReturnValue;

                    command.Parameters.Add("@EmployeeIDParm", SqlDbType.Int);
                    command.Parameters["@EmployeeIDParm"].Value = 1;

                    command.Parameters.Add("@FirstName", SqlDbType.VarChar, 40);
                    command.Parameters["@FirstName"].Direction = ParameterDirection.Output;

                    command.Parameters.Add("@LastName", SqlDbType.VarChar, 40);
                    command.Parameters["@LastName"].Direction = ParameterDirection.Output;

                    command.Parameters.Add("@JobTitle", SqlDbType.VarChar, 40);
                    command.Parameters["@JobTitle"].Direction = ParameterDirection.Output;

                    command.Parameters.Add("@MaxTotal", SqlDbType.Int);
                    command.Parameters["@MaxTotal"].Direction = ParameterDirection.Output;

                    command.ExecuteNonQuery();

                    if ((int)command.Parameters["@RetVal"].Value == 0) 
                    {
                        Debug.WriteLine(command.Parameters["@FirstName"].Value);
                        Debug.WriteLine(command.Parameters["@LastName"].Value);
                        Debug.WriteLine(command.Parameters["@JobTitle"].Value);
                        Debug.WriteLine(command.Parameters["@MaxTotal"].Value);
                    }
                }
            }
        }

So I guess I'll give up my quest for a way to use both ExecuteReader() and return and output parameters. Which kinda sucks, because I wanted to replace the SqlDataSource object above with an ObjectDataSource object, which means I probably have to execute two sprocs, one for the resultset, and one for the "return value" the SqlDataSource object natively knows about.

Best,

Bob

Imar · January 18th, 2008, 09:37 PM

Is it possible that you need to read both result sets and/or close your readers before you can access the return value? Something used to be the case with "classic" ADO...

Imar
---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

Bob Bedell · January 18th, 2008, 10:18 PM

I'LLLLLLLLLL BEEEEEEEEEEEEE DAMNEDDDDDDDDDDD!!!!!!!!!!!

Imar, can I be you in my next life????

60 hours latter...man...

Just move the return and output parameter access statments out of the DataReader's using block and into the Command object's using block. When the DataReader is disposed/closed, the values are available, both return values and output parameter values.

Code:

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 = 10;

                    using (SqlDataReader reader = command.ExecuteReader()) 
                    {
                        // Return value (and output parameters) are null when the reader is executed.
                        // 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());
                            }
                        }
                    }
                    // Return value (and output parameters) ARE ACCESSIBLE when the reader's
                    // Dispose method is called (i.e., its closed).
                    int returnValue = (int)command.Parameters["@pageCount"].Value;
                }

Thanks, man. I'd take a bullet for ya' for this one.

Best,

Bob

Bob Bedell · January 19th, 2008, 10:07 AM

[code]And that gives me a solution to my original problem: making the sprocs return value available to an ObjectDataSource in the same way its available to a SqlDataSource, enabling the reuse of the same sprocs.

I got from one of your previous replys, Imar, that whereas the return and output parameters of a SqlDataSource refer to the database object (i.e. the sproc), the return and output parameters of an ObjectDataSource refer to a business objectâs method signature. So now I can configure an ObjectDataSource as follows, and use the same stored procedures the SqlDataSource was using, with no re-writes.

Code:

<asp:ObjectDataSource ID="odsAnnouncementsList" runat="server" 
DataObjectTypeName=" Test.AnnouncementManager.BO.Announcement" 
          SelectMethod="GetList" OnSelected="odsAnnouncements_Selected"
            TypeName=" Test.AnnouncementManager.BLL.AnnouncementManager" >
<SelectParameters>
          <asp:ControlParameter Name="pageNum" ControlID="pn1" PropertyName="SelectedPage" />
            <asp:Parameter Name="pageSize" DefaultValue="10" Type="Int32" />
            <asp:Parameter Name="pageCount" Direction="Output" Type="Int32" />
</SelectParameters>
</asp:ObjectDataSource>

If, say, one was using a N-Layer design such as the one youâve detailed, you could now âcascadeâ the sprocâs return value back from the DAL by reference using method signature âoutâ parameters:

Code:

[DataObjectMethod(DataObjectMethodType.Select, true)]
public static AnnouncementList GetList(int pageNum, int pageSize, out int pageCount)
{
        return AnnouncementDB.GetList(pageNum, pageSize, out pageCount);
}

--->

public static AnnouncementList GetList(int pageNum, int pageSize, out int pageCount)
{
    using (SqlConnection connection = new SqlConnection(AppConfiguration.ConnectionString))
    {
          ...

            using (SqlCommand command = connection.CreateCommand())
            {
                  command.CommandType = CommandType.StoredProcedure; 
                  command.CommandText = "PagedAnnouncementList"; // Same sproc the SqlDataSource uses.

                  command.Parameters.Add("@pageNum", SqlDbType.Int).Value = pageNum;
                  command.Parameters.Add("@pageSize", SqlDbType.Int).Value = pageSize;
                  command.Parameters.Add("@pageCount", SqlDbType.Int);
                  command.Parameters["@pageCount"].Direction = ParameterDirection.ReturnValue;

                  using (SqlDataReader reader = command.ExecuteReader())
                  {
                      ...

                  }

                  // Assign RETURN value to 'pageCount' reference.  
                  pageCount = (int)command.Parameters["@pageCount"].Value;
           }
           // Return multiple result set.
           return announcementList;
}

Now the return value reference is available to ObjectDataSource events via the ObjectDataSourceStatusEventArgs OutputParameters collection.

protected void odsAnnouncements_Selected(object sender, System.Web.UI.WebControls.ObjectDataSourceStatusEv entArgs e)
{
      int pages;
      if (e.OutputParameters["pageCount"]!= null)
      {
            pages = (int)e.OutputParameters["pageCount"];
            pn1.Count = pages;
            Pn2.Count = pages;
       }
}

You had mentioned maybe including the sprocâs return value as a property of the returned collection, but I kindaâ like this approach because the sprocâs return value - in this case - doesnât belong to the business domain, and I didnât want to arbitrarily encapsulate it in a business object. Itâs a value the UI needs to configure a page numbering user control based on the size of the AnnouncementList collection.

Sorry I got a little excited earlier. But this really saves me a lot of stored procedure re-writing. Anyway, good to go for now.

Thanks again.

Bob

planoie · January 19th, 2008, 11:55 AM

I didn't catch it earlier but as Imar suggested it does sound like this problem was with the data reader. The DataReader maintains an active connection to the database. I don't know how it works internally but it's possible that at the point of the result set selection the process "pauses" until you have utilized all the available rows in the set. I know that with the DataReader the rows are retrieved from the data store as they are needed (i.e. when you do DataReader.Read()). so this would support the idea that the sproc return value isn't available yet.

I suspect that the SqlDataSource either applies this knowledge to utilize the return value or it gets the data as a DataSet/DataTable. Either way, by the time your program uses the data the connection to the database is completed and over so it gets back the return value and/or output parameters.

-Peter

Bob Bedell · January 19th, 2008, 04:26 PM

Hi Peter,

I found this helpful, too:

http://www.informit.com/guides/conte...net&seqNum=610

Apparently the Close method itself implements the code that pulls the return value, output parameter values, and also the 'rows affected' value out of the connected data stream. So it doesn't seem to be a matter of waiting for something to finish so that something else can become available, which is how I was thinking about it. The reason I didn't have the values I wanted is because I didn't ask for them, and the way you ask for them is by calling Close() (which the using block does implicitly). You can even break out of iterating through the result stream before reaching its end (using keyword 'break'), and calling Close() will still get the values in question.

Best,

Bob