?
Solved

Handling SQL's Output param vs. Return Value

Posted on 2005-05-05
5
Medium Priority
?
287 Views
Last Modified: 2010-04-07
SQL's BOL has an error handling sample that I'd like to reproduce but I'm not clear on how my .vb code will process a RETURN code vs an OUTPUT param. An output param is straight forward...my code just needs to create something like:

            Dim po1 As SqlParameter = New SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, False, CType(0, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing)

But how do i test for a RETURN VALUE?

DROP PROCEDURE SampleProcedure
GO
-- Create a procedure that takes one input parameter
-- and returns one output parameter and a return code.
CREATE PROCEDURE SampleProcedure @EmployeeIDParm INT,
            @MaxQuantity 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, Title
FROM Employees
WHERE EmployeeID = @EmployeeIDParm

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

-- Set a value in the output parameter.
SELECT @MaxQuantity = MAX(Quantity)
FROM [Order Details]

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

-- Returns 0 if neither SELECT statement had
-- an error, otherwise returns the last error.
RETURN @ErrorSave
GO
0
Comment
Question by:juststeve
  • 3
5 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 13934410
The same way you do with an Output param. Essentially the only difference in terms of how you handle the two in VB.Net is the type you specify when adding the Parameter to the Command object.

Hope this helps.
0
 
LVL 23

Expert Comment

by:b1xml2
ID: 13935703
1. you can test for this when you set the CommandType as StoredProcedure.
2. All stored procedures have as its first parameter the @RETURN_VALUE no mater how many other parameters it has.
3. When you set the CommandType to CommandType.StoredProcedure, a great majority of newbies to ADO.NET (but not necessarily to ASP.NET!!!) make the mistake of not adding the @RETURN_VALUE as the first parameter if they ahev handcrafted the stored procedure.
4. After executing the command, you can access the parameter like so: command.Parameters("@RETURN_VALUE").Value or command.Parameters(0).Value
5. If you have VS.NET, do yourself a favour and never ever write the code to plug into a stored procedure:


a. Create a component class.
b. Open Server Explorer.
c. Create a connection to your SQL Server.
d. Open your connection and access your stored procedure.
e. Drag and drop that onto your component class (in design mode)

0
 

Author Comment

by:juststeve
ID: 13994795
I'm still confused by the difference between a returned value that just returned by not declared in the param list (RETURN @ErrorSave) and one that's returned as originally defined (SELECT @MaxQuantity = MAX(Quantity)
FROM [Order Details]). Once I'm back at my page how can I test for the possible presence of the returned '@ErrorSave'?

Perhaps I shouldn't be explicitly declaring an output param at the sproc and just depend on ADO.NET's methods to return a RETURN_VALUE to the SqlParameter I define in my page?

0
 
LVL 23

Accepted Solution

by:
b1xml2 earned 2000 total points
ID: 13995356
ReturnValue (There can only be one)
========
RETURN @ErrorSave means the data there will be passed to the @RETURN_VALUE parameter in your stored procedure.
To access the return value, cmd.Parameters("@RETURN_VALUE").Value or cmd.Parameters(0).Value


Output Parameters (There can be many)
==========================
@MaxQuantity INT OUTPUT

To compare this to VB.NET

Function GetValue(ByVal text As String, ByRef number As Integer, ByRef checked As Boolean) As String
   number = 100
   checked = False
   Return text & "New"
End Function

and when you calll the Function

Dim a As String = "Test"
Dim b As Integer
Dim c As Boolean
Dim returnValue As String = GetValue(a,b,c)
Response.Write(b)
Response.Write(c)


Here in this function we passed in b and c by reference and get the values populated inside the function
We pass in the "a" variable by value and and a string value returned

this is similar to the following
create proc GetValue
@text varchar(10),
@number int output,
@checked bit output
as
declare @value varchar(50)
set @number = 100
set @checked = 0
set @value = @text + 'New'

0
 
LVL 23

Expert Comment

by:b1xml2
ID: 13995592
amendment
=======
create proc GetValue
@text varchar(10),
@number int output,
@checked bit output
as
declare @value varchar(50)
set @number = 100
set @checked = 0
set @value = @text + 'New'
return @value
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently went through the process of creating a Calendar Control of events with the basis of using a database to keep track of the dates that are selectable, one requirement was to have the selected date pop-up in a simple lightbox.  At first this…
IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question