6

I want to get both a return code and a result set back from a stored procedure in classic ASP.

CREATE PROCEDURE CheckEmployeeId
@EmployeeName nvarchar(255)
AS
BEGIN
    SET NOCOUNT ON;
DECLARE 
      @Exists       INT 
    , @RowCount     Int = 0
    , @ReturnValue  Int = 1


SELECT EmployeeId FROM Employees WHERE Name = @EmployeeName
set @RowCount = @@ROWCOUNT

if (@RowCount <> 1)
BEGIN
    SET @ReturnValue = 2 
END
ELSE
BEGIN
    SET @ReturnValue = 1
END

RETURN @ReturnValue 
END

So in ASP I can do the following to get the return value

Set cmd = CreateObject("ADODB.Command")
with cmd
    .ActiveConnection = cnnstr
    .CommandType = adCmdStoredProc
    .CommandText = "CheckEmployeeId"
    .Parameters.Refresh
    .Parameters("@EmployeeName")    = EmployeeName
end with
cmd.Execute()
RetVal = cmd.Parameters("@RETURN_VALUE")

or this to get the result set.

Set cmd = CreateObject("ADODB.Command")
with cmd
    .ActiveConnection = cnnstr
    .CommandType = adCmdStoredProc
    .CommandText = "CheckEmployeeId"
    .Parameters.Refresh
    .Parameters("@EmployeeName")    = EmployeeName
    Set rst = .Execute()
end with

Is there a way to get both?

user692942
  • 16,398
  • 7
  • 76
  • 175
David Elliott
  • 81
  • 1
  • 6
  • The return value is intended to return a status code of the execution, not to return values. If you want to receive values back from a procedure you should use OUTPUT parameters. – Sean Lange Feb 06 '17 at 14:22
  • @SeanLange they're using it to return the status of the execution, 2 for no records and 1 for some. – user692942 Feb 06 '17 at 14:30
  • @Lankymart I will politely disagree. That is not returning a status of the execution, it is returning a logical value. This is a great example of when we should use an output parameter. – Sean Lange Feb 06 '17 at 14:32
  • @SeanLange honestly you're splitting hairs, you want to be pedantic about it that's up to you. Even Microsoft don't agree - see [RETURN (Transact-SQL)](https://msdn.microsoft.com/en-gb/library/ms174998.aspx) - Example B. Returning status codes. – user692942 Feb 06 '17 at 14:35
  • @Lankymart probably correct. I tend to prefer using a output parameter instead. :) – Sean Lange Feb 06 '17 at 15:22
  • @SeanLange a lot of it is personal preference to be honest, I wasn't disagreeing with you just think *"not to return values"* is less an opinion and more a command. I'd probably use `OUTPUT` it this instance to be honest but each to their own. – user692942 Feb 06 '17 at 15:39
  • 1
    @Lankymart I certainly didn't take it any other way. As you said it is mostly preference. – Sean Lange Feb 06 '17 at 15:41

1 Answers1

6

You are already doing it just combine the two.

Set cmd = CreateObject("ADODB.Command")
with cmd
    .ActiveConnection = cnnstr
    .CommandType = adCmdStoredProc
    .CommandText = "CheckEmployeeId"
    .Parameters.Refresh
    .Parameters("@EmployeeName") = EmployeeName
    Set rst = .Execute()
end with
'You will need to close the Recordset before returning the RETURN_VALUE.
RetVal = cmd.Parameters("@RETURN_VALUE")

You don't need to choose one or the other the are independent of each other. The only issue will be the order they return, remember that both OUTPUT and RETURN values will not be accessible until all returned Recordsets are closed.

Personally, I prefer to close them straight away by storing them as 2 Dimensional Arrays.

Set cmd = CreateObject("ADODB.Command")
with cmd
    .ActiveConnection = cnnstr
    .CommandType = adCmdStoredProc
    .CommandText = "CheckEmployeeId"
    .Parameters.Refresh
    .Parameters("@EmployeeName") = EmployeeName
    Set rst = .Execute()
    If Not rst.EOF Then data = rst.GetRows()
    Call rst.Close()
end with
RetVal = cmd.Parameters("@RETURN_VALUE")

'Access Recordset array
If IsArray(data) Then
  'Return first column, first row.
  Response.Write data(0, 0)
End If
user692942
  • 16,398
  • 7
  • 76
  • 175
  • Sorry I always get a vartype of 0 (vbEmpty - Indicates Empty (uninitialized)) – David Elliott Feb 06 '17 at 14:44
  • 1
    @DavidElliott that's because the `Recordset` object has to be closed before you can read any `OUTPUT` or `RETURN` values. See [Value Property (ADO)](https://learn.microsoft.com/en-us/sql/ado/reference/ado-api/value-property-ado) specifically the Note box - *"If a command contains a Parameter whose Value property is empty, and you create a Recordset from the command, ensure that you first close the Recordset before retrieving the Value property."* – user692942 Feb 06 '17 at 14:46