从存储过程classic asp返回值和结果集 [英] Return a value and a result set from stored procedure classic asp

问题描述

我想从经典ASP中的存储过程中获取返回码和结果集。

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

因此在ASP中，我可以执行以下操作以获得返回值

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")

或以此获取结果集。

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

有办法吗

推荐答案

您已经在做，只需将两者结合即可。

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")

您不需要选择彼此独立的一个。唯一的问题将是它们返回的顺序，请记住，只有全部返回后， OUTPUT 和 RETURN 的值才能访问记录集已关闭。

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.

我个人更喜欢通过将它们存储为2维数组来立即关闭它们。

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

这篇关于从存储过程classic asp返回值和结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！