• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 313
  • Last Modified:

Handling SQL's Output param vs. Return Value

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
juststeve
Asked:
juststeve
  • 3
1 Solution
 
Carl TawnSystems and Integration DeveloperCommented:
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
 
b1xml2Commented:
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
 
juststeveAuthor Commented:
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
 
b1xml2Commented:
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
 
b1xml2Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now