定义用于执行存储过程的参数-经典ASP [英] Defining parameters for executing stored procedure - Classic ASP

问题描述

我正在尝试更新一些旧功能.有一个存储过程具有一个输入参数和两个输出参数.尝试执行代码时出现以下错误:

I am trying to update some old functionality. There is a stored procedure that has one input parameter and two output parameters. I am getting the following error when I try to execute the code:

Microsoft OLE DB Provider for ODBC Drivers error '80040e21'

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

/student.asp, line 30

存储过程:

ALTER PROCEDURE [Api].[GetKeyByAuthId]
    @AuthenticationId uniqueidentifier
,   @Key int = NULL OUTPUT
,   @type varchar(25) = NULL OUTPUT

直接通话效果很好:

USE [Development]
GO

DECLARE @return_value int,
        @Key int,
        @type varchar(25)

EXEC    @return_value = [Api].[GetKeyByAuthId]
        @AuthenticationId = '0550F579-DBDA-4C41-82B3-453841A6232E',
        @Key = @Key OUTPUT,
        @type = @type OUTPUT

SELECT  @Key as N'@Key',
        @type as N'@type'

SELECT  'Return Value' = @return_value

GO

身份验证ID是唯一的标识符，并通过页面上的查询字符串接收.这是我的代码，第30行是cmd.Execute:

Authentication Id is unique identifier and is received via query string on the page. Here is my code, line 30 is cmd.Execute:

var cmd = Server.CreateObject("ADODB.Command");
cmd.CommandText = "Api.GetKeyByAuthId";
cmd.CommandType = 4;   // Stored procedure


var p = cmd.Parameters;
p.Append(cmd.CreateParameter("@AuthenticationId", 72, 1));
p.Append(cmd.CreateParameter("@Key", 3, 2, 9));
p.Append(cmd.CreateParameter("@type", 200, 2, 25));

cmd("@AuthenticationId") = aid;

cmd.ActiveConnection = Conn;
cmd.Execute();

Key = cmd.Parameters("@Key");
type = cmd.Parameters("@type");

推荐答案

在您提到存储过程期望@Key作为Int，但是该字段的基础表数据类型是BigInt.

You mentioned that the Stored Procedure expects @Key as an Int but the underlying table data type for the field is BigInt.

警告

此方法的问题是从BigInt到Int的转换很危险(请参阅

The problem with this approach is that the conversion from BigInt to Int is dangerous (see SQL - safely downcast BIGINT to INT). Most of the time you will get

Msg 8115, Level 16, State 2, Line 5
Arithmetic overflow error converting expression to data type int.

您可以使用

SET ANSI_WARNINGS OFF -- Divide-by-zero and arithmetic overflow errors cause null values to be returned
SET ARITHABORT OFF    -- Don't terminate the query on Divide-by-zero and arithmetic overflow errors
SET ARITHIGNORE ON    -- Suppress errors from Divide-by-zero and arithmetic overflow errors

我的猜测是

Multiple-step OLE DB operation generated errors

存储过程中的

错误是由Arithetic overflow error引起的，因为您在@Key中检索的值太大而无法容纳Int数据类型.

error in your Stored Procedure is being caused by an Arithetic overflow error because the value you are retrieving in @Key is too big to fit in an Int data type.

在可能的情况下，我将与您的开发人员联系并解释情况，并查看是否可以修改存储过程以将@Key传递为BigInt.

When possible I would speak to your developers and explain the situation and see whether the Stored Procedure can be modified to pass @Key as BigInt.

这篇关于定义用于执行存储过程的参数-经典ASP的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！