juststeve
asked on
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_VALU
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
Dim po1 As SqlParameter = New SqlParameter("@RETURN_VALU
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
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.StoredProcedur
4. After executing the command, you can access the parameter like so: command.Parameters("@RETUR
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)
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.StoredProcedur
4. After executing the command, you can access the parameter like so: command.Parameters("@RETUR
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)
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
=======
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
Hope this helps.