none
JScript获取存储过程的output变量 RRS feed

  • 问题

  • 存储过程:
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[RunStoredProcedures]
    @SQLQuery nvarchar(4000),
    @ReturnCd int output
    AS
    BEGIN
    SET NOCOUNT ON;
    exec sp_executesql @SQLQuery, N'@ReturnCd int output', @ReturnCd output
    END

    代码:
        var conn = new ActiveXObject("ADODB.Connection");
        var cmd = new ActiveXObject("ADODB.Command");
        var sqlQurey;
        var connectionString = "Provider=SQLOLEDB.1; Data Source=" + exceptionDBServer + "; User ID=" + userID +
                " ;Password=" + password + "; Initial Catalog=" + exceptionDB;
        conn.open(connectionString);
        sqlQurey = "RunStoredProcedures";
        cmd.ActiveConnection = conn;
        cmd.CommandType = 4;
        cmd.CommandText = sqlQurey;
        cmd.Parameters.Append(cmd.CreateParameter("@SQLQuery", 203, 1, 4000, "exec @ReturnCd=SimulationExceptionLoad N'DSTISERVER1',N'T85RB',N'DSTISERVER1',N'T85RW',1930,0"));
        cmd.Parameters.Append(cmd.CreateParameter("@return_value", 3, 2));
        try {
            cmd.execute;
            WScript.Echo("#############################################");
            WScript.Echo(String(cmd.Parameters(0)));
            WScript.Echo(String(cmd.Parameters(1)));
            WScript.Echo("#############################################");
        }
        catch (err) {
            WScript.Echo(err);
        }

    存储过程跑完了,为什么cmd.Parameters(1)一直是空的阿???
    2009年6月9日 5:24

答案

全部回复

  • 你好  你这里的参数 cmd.Parameters.Append(cmd.CreateParameter("@return_value", 3, 2));
    参数名最好改为@ReturnCd

    cmd.Parameters.Append(cmd.CreateParameter("@ReturnCd", 3, 2));


    创建参数实例:

    var objparameter=objcommand.CreateParameter (name,type,direction,size,value)
    其中

    direction取值:
    ConstantValueDescription
    adParamUnknown 0 Direction is unknown
    adParamInput 1 Input parameter
    adParamOutput 2 Output parameter
    adParamInputOutput 3 Both input and output parameter
    adParamReturnValue 4 Return value

    type取值:
    ConstantValueDescription
    adEmpty 0 No value
    adSmallInt 2 A 2-byte signed integer.
    adInteger 3 A 4-byte signed integer.
    adSingle 4 A single-precision floating-point value.
    adDouble 5 A double-precision floating-point value.
    adCurrency 6 A currency value
    adDate 7 The number of days since December 30, 1899 + the fraction of a day.
    adBSTR 8 A null-terminated character string.
    adIDispatch 9 A pointer to an IDispatch interface on a COM object. Note: Currently not supported by ADO.
    adError 10 A 32-bit error code
    adBoolean 11 A boolean value.
    adVariant 12 An Automation Variant. Note: Currently not supported by ADO.
    adIUnknown 13 A pointer to an IUnknown interface on a COM object. Note: Currently not supported by ADO.
    adDecimal 14 An exact numeric value with a fixed precision and scale.
    adTinyInt 16 A 1-byte signed integer.
    adUnsignedTinyInt 17 A 1-byte unsigned integer.
    adUnsignedSmallInt 18 A 2-byte unsigned integer.
    adUnsignedInt 19 A 4-byte unsigned integer.
    adBigInt 20 An 8-byte signed integer.
    adUnsignedBigInt 21 An 8-byte unsigned integer.
    adFileTime 64 The number of 100-nanosecond intervals since January 1,1601
    adGUID 72 A globally unique identifier (GUID)
    adBinary 128 A binary value.
    adChar 129 A string value.
    adWChar 130 A null-terminated Unicode character string.
    adNumeric 131 An exact numeric value with a fixed precision and scale.
    adUserDefined 132 A user-defined variable.
    adDBDate 133 A date value (yyyymmdd).
    adDBTime 134 A time value (hhmmss).
    adDBTimeStamp 135 A date/time stamp (yyyymmddhhmmss plus a fraction in billionths).
    adChapter 136 A 4-byte chapter value that identifies rows in a child rowset
    adPropVariant 138 An Automation PROPVARIANT.
    adVarNumeric 139 A numeric value (Parameter object only).
    adVarChar 200 A string value (Parameter object only).
    adLongVarChar 201 A long string value.
    adVarWChar 202 A null-terminated Unicode character string.
    adLongVarWChar 203 A long null-terminated Unicode string value.
    adVarBinary 204 A binary value (Parameter object only).
    adLongVarBinary 205 A long binary value.
    AdArray 0x2000 A flag value combined with another data type constant. Indicates an array of that other data type.


    努力着就不会放弃!http://hi.baidu.com/1987raymond
    2009年6月9日 5:36
    版主
  • 改成了cmd.Parameters.Append(cmd.CreateParameter("@ReturnCd", 3, 2)); 但是还是空的
    2009年6月9日 5:53
  • 你检查下你的 这个 SimulationExceptionLoad  是不是这个地方出了问题? 不知道是什么意思 你自定义的函数?
    努力着就不会放弃!http://hi.baidu.com/1987raymond
    2009年6月9日 5:59
    版主
  • SimulationExceptionLoad 是个存储过程,没有问题
    2009年6月9日 6:00
  • 问题解决~~~~
    游标没有设置
    2009年6月9日 6:23
  • --------------------------------------------------------------------------------------
    -- Script
    --------------------------------------------------------------------------------------
    <script type="text/javascript">
    var conn = new ActiveXObject("ADODB.Connection");
    var cmd = new ActiveXObject("ADODB.Command");
    var sqlQurey;
    var connectionString = "Provider=SQLOLEDB.1; Data Source=XXY\\SQLEXPRESS; User ID=sa ;Password=sa; Initial Catalog=XSample"
    conn.open(connectionString);
    sqlQurey = "XPramaterValue";
    cmd.ActiveConnection = conn;
    cmd.CommandType = 4;
    cmd.CommandText = sqlQurey;
    cmd.Parameters.Append(cmd.CreateParameter("@fKey", 3, 2));
    try
    {
        cmd.execute;
        alert(cmd.Parameters(0));
    }
    catch (err)
    {
        alert(err.message);
    }
    </script>

    --------------------------------------------------------------------------------------
    -- 存储过程
    --------------------------------------------------------------------------------------
    CREATE PROC XPramaterValue
    (
    	@fKey INT OUTPUT
    )
    AS
    	SET @fKey = 10

    2009年6月9日 6:24
    版主