Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 18th, 2008, 02:25 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default Problem with sproc

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)


 
Old January 18th, 2008, 02:34 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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

 
Old January 18th, 2008, 04:24 PM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

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
 
Old January 18th, 2008, 07:27 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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




 
Old January 18th, 2008, 09:02 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

 
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

 
Old January 18th, 2008, 09:37 PM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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.
 
Old January 18th, 2008, 10:18 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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

 
Old January 19th, 2008, 10:07 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

[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

 
Old January 19th, 2008, 11:55 AM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

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
 
Old January 19th, 2008, 04:26 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
dynamic sproc sabry SQL Language 1 August 25th, 2006 06:23 AM
When a sproc or table was Last Used dlandolin SQL Server 2000 1 September 29th, 2005 08:09 PM
Working with Dates in SPROC hugh@kmcnetwork.com SQL Language 4 March 10th, 2004 03:56 PM
Optional WHERE clause in sproc KYC SQL Language 2 February 17th, 2004 03:40 PM
WHERE BETWEEN search condition in sproc jtyson SQL Server 2000 1 August 7th, 2003 09:22 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.