March 17, 2004 at 12:18 pm
Hi,
thanks for reading!
Here is my problem: I have a strored procedure that inserts some records
into one table and then selects some records from another table at the end.
The stored procedure takes several parameters, first one of them is marked as
OUTPUT. I'm using it to return an id of the inserted record. The procedure is called from asp.net code. Now for the problem: if the the select statement at the end returns 0 records everything works and i my first parameter contains the @@IDENTITY value from the insert statement like it is supposed to.
If the select statement at the end returns 1 or more records my output parameter is not updated at all and contains the same value as before the procedure was run. All the records are inserted correctly. I hope you can shed some light on this for me. Here is my stored procedure:
CREATE PROCEDURE cwSaveProductInquiry
@inquiryId int OUTPUT,
@libraryName nvarchar(500),
@contactName nvarchar(200),
@address nvarchar(100),
@city nvarchar(50),
@state nvarchar(3),
@zip nvarchar(10),
@phone nvarchar(50),
@email nvarchar(100),
@comment nvarchar(3000),
@productIds nvarchar(2000)
AS
INSERT INTO INQUIRY (LibraryName, ContactName, Address, City, State, Zip, Phone, Email, Comment) VALUES(@libraryName, @contactName, @address, @city, @state, @zip, @phone, @email,@comment)
--i tried including this statement at the end as well but that did not do the
--trick either
select @inquiryId=@@IDENTITY FROM INQUIRY
set nocount on
declare @separator_position int -- This is used to locate each separator character
declare @objectId varchar(200) -- this holds each array value as it is returned
if(@productIds is not null)
begin
while patindex('%,%' , @productIds) <> 0
begin
select @separator_position = patindex('%,%' , @productIds)
select @objectId= left(@productIds, @separator_position - 1)
INSERT INTO PRODUCT_INQUIRY_LOOKUP (ProductId,InquiryId) VALUES(@objectId, @inquiryId)
select @productIds = stuff(@productIds, 1, @separator_position, '')
end
end
set nocount off
Select Distinct Email from vPRODUCT_CONTACT WHERE ProductId in
(Select ProductId From Product_Inquiry_Lookup Where InquiryId=@inquiryId)
GO
March 17, 2004 at 2:21 pm
I think your suffering from having multiple rowsets problem generated from your procedure. While in your case its just a single rowset. You need to flush the rowset from the output stream in order to get at the OUTPUT parameters in the stream. I think your problem will be resolved by making sure you consume the rowset from the server in your ASP.NET code, because I assuming ADO.NET is like ADO in that the rowset data will precede the stored procedure date in the rowset stream. I can't imagine that ado.net is that different when interfacing with the SQL Server engine than the old ADO/OLEDB/SQLSERVER interface stack.
Peter Evans (__PETER Peter_)
March 17, 2004 at 2:34 pm
Peter, thanks for you reply. Could you elaborate on what you mean by consuming a rowset?
do you mean something like myreader.NextResult() ?
before i start reading the values?
Thanks
March 17, 2004 at 10:42 pm
You were on the right track, but it sounds like a command object is warranted
(whatever that may be in ADO.NET). I swear there's been so many sql server
api's over the years they begin to blur in the head. You know I guess this problem
wasn't an ADO problem it was more of db-lib problem. But it looks like its
reincarnated back with the thinness of ADO.NET. This is an overall good thing,
but leads to your point of confusion. Here's the snippet from the this msdn article related to your issue.
Peter Evans (__PETER Peter_)
William Vaughn
Beta V Corporation
April 2003
Applies to:
Microsoft ADO.NET
Microsoft SQL Server™
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/gazoutas.asp
If you want to capture the integer passed back by the RETURN statement in TSQL
or your stored procedure's OUTPUT parameters, you're going to have
to take another few minutes to setup a Command object to capture these values f
rom the resultset. Again, and I can't emphasis this enough, these values are not made available
until you've processed all rowsets in each resultset (if there are any). Yes, a stored procedure
can return several resultsets, but the RETURN value and OUTPUT parameters are not populated
until all queries have been completed and all rowsets transmitted back to the client.
Okay, so you asked for too many rows in one of your queries and you want to step over
this rowset to get to your OUTPUT parameters when working with a DataReader.
Don't be tempted to simply close the DataReader without using the Cancel method
on the Command first. That's because ADO.NET completes the processing of any remaining rows
instead of telling SQL Server to cancel all further query processing for this Command.
The following code constructs a DataAdapter with its associated SelectCommand to execute
a multiple-resultset stored procedure that returns several OUTPUT parameters.
Note that the parameters must be named correctly when working with SQL Server.
That is they match the names used in the stored procedure. Order is not important,
but you must include any parameter that does not have a default value set in the stored procedure.
Listing 4. Constructing a Command and using Fill to execute the query
Dim bolEOF As Booleancn = New SqlConnection("server=demoserver;" & _
"database=Pubs;integrated security=sspi")CreateTestProcedure()
' Create test SP in Pubs DBda = New SqlDataAdapter("OutputTitleInfoByTID", cn)
da.SelectCommand.CommandType = CommandType.StoredProcedureWith
da.SelectCommand.Parameters
.Add("@TID", SqlDbType.VarChar, 6).Value = "BU1032"
.Add("@Title", SqlDbType.VarChar, 80).Direction _
= ParameterDirection.Output
.Add("@Type", SqlDbType.VarChar, 40).Direction _
= ParameterDirection.Output
.Add("@Price", SqlDbType.Money).Direction _
= ParameterDirection.Output
.Add("@Advance", SqlDbType.Money).Direction _
= ParameterDirection.Output
.Add("@ReturnValue", SqlDbType.Int).Direction _
= ParameterDirection.ReturnValue
ds = New DataSet da.Fill(ds)Once the Fill method executes the query and processes the rowsets, the RETURN value and OUTPUT parameters
are available through the SelectCommand.Parameters collection either by ordinal position or by name as shown in listing 5.
Listing 5. Displaying the values of the Return Value and OUTPUT parameters
Debug.WriteLine("@ReturnValue:" & _
.Item("@ReturnValue").Value.ToString)Debug.WriteLine("@Title:" & _
.Item("@Title").Value.ToString)Debug.WriteLine("@Type:" & _
.Item("@Type").Value.ToString)Debug.WriteLine("@Advance:" & _
.Item("@Advance").Value.ToString)Debug.WriteLine("@Price:" & _
.Item("@Price").Value.ToString)Debug.WriteLine("@Advance:" & _
.Item("@Advance").Value.ToString)This same query can be executed directly with the Command.ExecuteReader (or ExecuteNonQuery
if you don't have a rowset to process), but there are several other steps you'll need to take
to process the returned values. Remember, you'll have to complete processing for all rowsets before attempting
to capture the Return value or OUTPUT parameters. The following code shows how to use the ExecuteReader and
a loop to process the rowsets, and then capture the Return value and OUTPUT parameters.
You'll find that OUTPUT parameters (even a lot of them) can be handled far faster than even a single row of data returned by a SELECT.
Listing 6. Displaying the values of the Return Value and OUTPUT parameters
With cmd.Parameters cn.Open()
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
' Process rowset(s)
bolEOF = dr.Read
Do
Do While bolEOF = True ' Process rows
bolEOF = dr.Read()
Loop
Loop While dr.NextResult = True
cmd.Cancel()
dr.Close()
Debug.WriteLine("@ReturnValue:" & _
.Item("@ReturnValue").Value.ToString)
Debug.WriteLine("@Title:" & _
.Item("@Title").Value.ToString)
Debug.WriteLine("@Type:" & _
.Item("@Type").Value.ToString)
Debug.WriteLine("@Advance:" & _
.Item("@Advance").Value.ToString)
Debug.WriteLine("@Price:" & _
.Item("@Price").Value.ToString)End WithThis fairly brief article walked you through the details of handling resultsets and the rowsets and
other stuff they contain. You shouldn't have trouble getting to the gazoutas after this—at least I hope not.
March 18, 2004 at 5:53 am
Thanks Peter.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply