fetch data from output parameter of a store procedure

[Guest]

Hi everyone,
I wrote a store procedure that fetch one row data in the database based on
the parameter value I entered. After I created the store procedure,
the store procedure code looks like this:
ALTER proc getProductCommScale @product As varchar(30), @TISCommRate As
Decimal(5,2) OUTPUT,
@BrokerCommRate As Decimal(5,2) OUTPUT, @Fee As Decimal(5,2) OUTPUT
As
if RTRIM(@product)='Imed'
Select @TISCommRate=TISComm, @BrokerCommRate=BrokerComm, @Fee=Fee
from tis_productCommScale where ProductName='Imed'
select @TISCommRate , @BrokerCommRate, @Fee
Return
an dthen I tested in the database with the following code:
Declare @TISCommRate As Decimal(5,2)
Declare @BrokerCommRate As Decimal(5,2)
Declare @Fee As Decimal(5,2)
exec getProductCommScale 'Vusa',@TISCommRate, @BrokerCommRate, @Fee
Then I got the following data:
..35 .20 5.00

But it seems not working in asp.net(1.1), here is part of my .net coding:
Dim sqlConnection As SqlConnection = New
SqlConnection(ConfigurationSettings.AppSettings(Global.CfgKeyConnString))
Dim myCommand As SqlCommand = New
SqlCommand("GetProductCommScale", sqlConnection)

myCommand.CommandType = CommandType.StoredProcedure
Dim pProduct As SqlParameter = New SqlParameter("@Product",
SqlDbType.VarChar, 30)
pProduct.Value = product
myCommand.Parameters.Add(pProduct)

Dim pTisCommRate As SqlParameter = New
SqlParameter("@TISCommRate", SqlDbType.Decimal)
pTisCommRate.Precision = 5
pTisCommRate.Scale = 2
pTisCommRate.Direction = ParameterDirection.Output
myCommand.Parameters.Add(pTisCommRate)

Dim pBrokerCommRate As SqlParameter = New
SqlParameter("@BrokerCommRate", SqlDbType.Decimal)
pBrokerCommRate.Precision = 5
pBrokerCommRate.Scale = 2
pBrokerCommRate.Direction = ParameterDirection.Output
myCommand.Parameters.Add(pBrokerCommRate)

Dim pFee As SqlParameter = New SqlParameter("@Fee",
SqlDbType.Decimal)
pFee.Precision = 5
pFee.Scale = 2
pFee.Direction = ParameterDirection.Output
myCommand.Parameters.Add(pFee)

sqlConnection.Open()
Dim reader As SqlDataReader = myCommand.ExecuteReader()

Dim TisCommRate As Decimal = Convert.ToDecimal(pTisCommRate.Value)

But somehow I always get nothing from pTisCommRate.Value, same to the other
parameters. What's going on? I am just learning to get data from output
parameters, did I miss anything?

 

[Guest]

Sorry, I use the wrong one: exec getProductCommScale 'Vusa',@TISCommRate,
@BrokerCommRate, @Fee
Acutally I mean exec getProductCommScale 'Imed,@TISCommRate,
@BrokerCommRate, @Fee

 

[Guest]

Hi everyone,
I got it. It is because I added one more line code in the Store Procedure
which is

select ,@TISCommRate, @BrokerCommRate, @Fee
It is used for displaying data after the execution of the store procedure.
After I removed the line of code, the data in the my asp.net fine now.
Can you tell me why?

 

[Guest]

I got it, it is because of the extra line of code in the store procedure
select @TISCommRate, @BrokerCommRate, @Fee
for displaying execution result from the store procedure. After I removed
this line of code, it is fine in asp.net.
Can you tell me why?

 

[Steven Cheng[MSFT]]

Hi Betty,

As for the store procedure output parameter behavior you encountered, it is
due to the following reason:

When you add the addition statement below in your SP:

select ,@TISCommRate, @BrokerCommRate, @Fee

after you executed the stored precedure, it will return three results:

1. three columes resulted by the above statement, you can change the
statement to

"select ,@TISCommRate as AA , @BrokerCommRate as BB , @Fee as CC" to
distinct with your output paramters

2. the output parameters defined in your SP( @TISCommRate,
@BrokerCommRate, @Fee)

3. the return value of your store procedure.

You can verify this by executing the SP in SQL Server management studio or
query analyzer.

When you execute the SP through .NET SqlCommand object, the returned
DataReader will also contains the three results, and the #1 one is in ahead
of the output parameters, therefore, you need to use the
DataReader.NextResult to go through the previous result and query the
parameters. e.g:

===================
................

Dim reader As SqlDataReader
reader = comm.ExecuteReader()

'get the first result

reader.NextResult()



For Each pm As SqlParameter In comm.Parameters

If pm.Direction = ParameterDirection.Output Then
Response.Write("<br/>" & pm.ParameterName & ": " & pm.Value)
End If


Next

reader.Close()
conn.Close()
=================================

another means to get the output parameter is access the Parameters
collection after you have closed the datareader, at that time, those output
parameter has been filled with the proper return values. e.g.

===================
................

Dim reader As SqlDataReader
reader = comm.ExecuteReader()

...........

reader.Close()

' access the output parameters here

=================================

Hope this helps clarify it. If you have anything unclear, please feel free
to let me know.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead



==================================================

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.



Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.

==================================================



This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Guest]

Steve,
thank you for the very clear clarification.
Nice to meet you here again.

 

[Steven Cheng[MSFT]]

Me too

Have a nice day!

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no rights.