从sp（十字架）返回 [英] returning from sp(cross)

问题描述

CREATE PROCEDURE st_deneme @ userid numeric（18），@ result numeric（18）output

AS

select * from users

return 10

GO

**************************** br />
<！ - METADATA TYPE =" typelib"

NAME =" Microsoft ActiveX Data Objects 2.8 Library"

UUID =" {2A75196C-D9EB-4129-B803-931327F72D5C}"

VERSION =" 2.8" - >

<！ - #include file =" ; conn.asp" - >

<％

设置cmd = server.CreateObject（" ADODB.Command"）

cmd.CommandType = 4

cmd.ActiveConnection = baglantim

cmd.CommandText =" st_deneme"

cmd.CreateParameter" @ userid" ，adNumeric，adParamInput，100,100

cmd.CreateParameter" @ result" ，adNumeric，adParamOutput，18,18

''cmd.CreateParameter" @ result"，adNumeric，adParamReturnValue，18,18

cmd.Parameters（" @ userid" ;）。值= 1

xx = cmd.Parameters（" @ result"）。值

设置objRS = cmd.Execute

Response.Write objRS.Fields（" email"）。Value&"< br>"

Response.Write xx

％>


不返回任何值..问题是什么？

CREATE PROCEDURE st_deneme @userid numeric (18) ,@result numeric (18) output
AS
select * from users
return "10"
GO
****************************
<!--METADATA TYPE="typelib"
NAME="Microsoft ActiveX Data Objects 2.8 Library"
UUID="{2A75196C-D9EB-4129-B803-931327F72D5C}"
VERSION="2.8"-->
<!--#include file="conn.asp"-->
<%
Set cmd=server.CreateObject("ADODB.Command")
cmd.CommandType=4
cmd.ActiveConnection=baglantim
cmd.CommandText = "st_deneme"
cmd.CreateParameter "@userid", adNumeric, adParamInput,100 ,100
cmd.CreateParameter "@result" ,adNumeric ,adParamOutput ,18 , 18
''cmd.CreateParameter "@result",adNumeric ,adParamReturnValue ,18,18
cmd.Parameters ("@userid").Value =1
xx=cmd.Parameters ("@result").Value
Set objRS = cmd.Execute
Response.Write objRS.Fields("email").Value&"<br>"
Response.Write xx
%>

dont return any value .. what is the problem?

推荐答案

sp中的第一个语句应该是SET NOCOUNT ON以避免

消息指示受t-sql语句影响的行数是

作为结果的一部分返回。如果您不想这样做，那么您有

使用记录集方法NEXTRECORDSET来获得您感兴趣的结果

in。要访问输出参数和返回值，你已经处理了

记录集或首先取消它，因为SQL Server返回输出参数并且

返回它返回给客户端的最后一个数据包中的代码值。


AMB


Savas Ates写道：

The first statement in the sp should be SET NOCOUNT ON to avoid that the
message indicating the number of rows affected by the t-sql statement be
returned as part of the result. If you do not want to do this, then you have
to use recordset method NEXTRECORDSET to get the result you are interested
in. To access output parameters and return value, you have process the
recordset or cancel it first, because SQL Server returns output parameter and
return code values in the last packet it returns to the client.

AMB

"Savas Ates" wrote:

创建程序st_deneme @userid数字（18），@结果数字（18）输出
AS
select * from users
return" ; 10"
GO
****************************

< ！ - METADATA TYPE =" typelib"
NAME =" Microsoft ActiveX Data Objects 2.8 Library"
UUID =" {2A75196C-D9EB-4129-B803-931327F72D5C}"
VERSION =" 2.8" - >
<！ - #include file =" conn.asp" - >
<％
设置cmd =服务器。 CreateObject（" ADODB.Command"）
cmd.CommandType = 4
cmd.ActiveConnection = baglantim
cmd.CommandText =" st_deneme"
cmd.CreateParameter" @ userid" ;，adNumeric，adParamInput，100,100
cmd.CreateParameter" @ result" ，adNumeric，adParamOutput，18,18
''cmd.CreateParameter" @ result"，adNumeric，adParamReturnValue，18,18
cmd.Parameters（" @ userid"）。Value = 1
xx = cmd.Parameters（" @ result"）。值
设置objRS = cmd.Execute
Response.Write objRS.Fields（" email"）。Value&"< br> ;"
Response.Write xx
％>

不返回任何值..有什么问题？

CREATE PROCEDURE st_deneme @userid numeric (18) ,@result numeric (18) output
AS
select * from users
return "10"
GO
****************************
<!--METADATA TYPE="typelib"
NAME="Microsoft ActiveX Data Objects 2.8 Library"
UUID="{2A75196C-D9EB-4129-B803-931327F72D5C}"
VERSION="2.8"-->
<!--#include file="conn.asp"-->
<%
Set cmd=server.CreateObject("ADODB.Command")
cmd.CommandType=4
cmd.ActiveConnection=baglantim
cmd.CommandText = "st_deneme"
cmd.CreateParameter "@userid", adNumeric, adParamInput,100 ,100
cmd.CreateParameter "@result" ,adNumeric ,adParamOutput ,18 , 18
''cmd.CreateParameter "@result",adNumeric ,adParamReturnValue ,18,18
cmd.Parameters ("@userid").Value =1
xx=cmd.Parameters ("@result").Value
Set objRS = cmd.Execute
Response.Write objRS.Fields("email").Value&"<br>"
Response.Write xx
%>

dont return any value .. what is the problem?

很抱歉看起来好像我在回复你的亚历杭德罗，但我没有看到

OP理由。

Alejandro Mesa写道：

I''m sorry it seems like I''m replying to you Alejandro, but I don''t see the
OP for some reason.
Alejandro Mesa wrote:

Savas Ates写道：

"Savas Ates" wrote:

创建过程st_deneme @userid数字（18），


数值和十进制数据类型应该有两个属性：Precision和

规模。你只提供了一个属性，精度（18），因此默认情况下，比例将为

假设为零。这是你的意图吗？更接受

声明此变量的方式是：


@ userid numeric（18,0）

@result numeric
（18）输出AS
select * from users
return" 10"
GO
*************** *************


输出参数的目的是什么？你没有为你的程序中的值分配

。您可能会发现这篇文章很有价值：

http://www.google.com/groups?hl=en&l...TNGP10.phx.gbl


<％
设置cmd = server.CreateObject（" ADODB.Command"）
cmd.CommandType = 4
cmd.ActiveConnection = baglantim
cmd.CommandText =" st_deneme"
cmd.CreateParameter" @ userid"，adNumeric，adParamInput，100,100


你为什么要提供 100 QUOT;作为这个参数的长度？因为数字参数忽略了长度参数，所以它没有什么价值，但是它让我怀疑你不明白这些<的目的<
br />
参数。转到msdn.microsoft.com/library并找到ADO文档

（深入了解TOC中的数据访问节点）。查看

CreateParameter方法。


数字和十进制参数必须在分配之前设置其精度和数字级别

属性对他们有价值。这不能在

CreateParameter语句中完成。见下文。


cmd.CreateParameter" @ result" ，adNumeric，adParamOutput，18,18
''cmd.CreateParameter" @ result"，adNumeric，adParamReturnValue，18,18


除了创建参数对象外，你必须将它们附加到

参数集合中。见下文。

cmd.Parameters（" @ userid"）。值= 1
xx = cmd.Parameters（" @ result"）。值


现在尝试从输出参数中获取值太早了。你

还没有执行这个程序！


设置objRS = cmd.Execute
Response.Write objRS.Fields（"电子邮件"）值及安培;。"<峰; br>"


您必须在尝试读取

输出参数的值之前关闭记录集（输出和返回值不会发送到客户端，直到

程序生成的所有结果集的所有记录都会被发送。

他认为按照Alejandro的建议使用SET NOCOUNT ON非常重要。）

Response.Write xx
％>

CREATE PROCEDURE st_deneme @userid numeric (18) ,
Numeric and Decimal datatypes should have two attributes: Precision and
scale. You''ve only provided one attribute, precision (18), so scale will be
assumed to be zero by default. Is that what you intended? The more accepted
way of declaring this variable would be:

@userid numeric (18,0)
@result numeric
(18) output AS
select * from users
return "10"
GO
****************************
What is the purpose of the output parameter? You''re not assigning a value to
it in your procedure. You might find this post to be of value:

http://www.google.com/groups?hl=en&l...TNGP10.phx.gbl


<%
Set cmd=server.CreateObject("ADODB.Command")
cmd.CommandType=4
cmd.ActiveConnection=baglantim
cmd.CommandText = "st_deneme"
cmd.CreateParameter "@userid", adNumeric, adParamInput,100 ,100
Why are you providing "100" as the length of this parameter? It doesn''t
matter since the length argument is ignored for numeric parameters, but it
leads me to suspect that you don''t understand the purpose of these
arguments. Go to msdn.microsoft.com/library and find the ADO documentation
(drill down into the Data Access node in the TOC). look up the
CreateParameter method.

Numeric and Decimal parameters must have their Precision and NumericScale
properties set before you assign a value to them. This cannot be done in the
CreateParameter statement. See below.

cmd.CreateParameter "@result" ,adNumeric ,adParamOutput ,18 , 18
''cmd.CreateParameter "@result",adNumeric ,adParamReturnValue ,18,18
In addition to creating the parameter objects, you have to append them to
the Parameters collection. See below.
cmd.Parameters ("@userid").Value =1
xx=cmd.Parameters ("@result").Value
This is too soon to try and get a value from your output parameter. You
haven''t even executed the procedure yet!

Set objRS = cmd.Execute
Response.Write objRS.Fields("email").Value&"<br>"
You must close the recordset before attempting to read the value of the
output parameter (output and return values are not sent to the client until
all the records for all the resultsets generated by the procedure are sent.
his makes it important to use SET NOCOUNT ON as suggested by Alejandro.)
Response.Write xx
%>

除了Alejandro所说的，创建和
$ b $通常是正确的。 b首先附加返回参数，即使您不打算使用它。我通常称它为RETURN_VALUE，以区别于其他参数。

这是它应该是什么样的，假设你会修改你的

通过输出参数而不是

返回语句返回值的过程：


使用cmd.Parameters

.Append cmd.CreateParameter（" RETURN_VALUE"，_

adInteger，adParamReturnValue）

set param = .CreateParameter（" @ userid"，adNumeric，_

adParamInput）

param.Precision = 18

param.NumericScale = 0

param.value = 1

.Append param

set param = .CreateParameter（" @ result"，adNumeric，_

adParamOutput）

param.Precision = 18

param.NumericScale = 0

.Append param

结束

设置objRS = cmd.Execute

Response.Write objRS.Fields（" email"）。Value&"< br>"

objRS.clos e：设置objRS = Nothing

xx = cmd.Parameters（" @ result"）。值

Response.Write xx


我已经编写了一个免费工具来生成执行

存储过程所需的vbscript代码。你会发现它很有用。它可以在这里找到：
http://www.thrasherwebdesign.com/ind...s&hp=links.asp


HTH，

Bob Barrows

-

Microsoft MVP - ASP / ASP.NET

请回复新闻组。我的From

标题中列出的电子邮件帐户是我的垃圾邮件陷阱，因此我不经常检查它。通过发布到新闻组，您将获得更快的回复。

In addition to what Alejandro said, it''s usually correct to create and
append the return parameter first, even if you aren''t planning to use it. I
usually call it RETURN_VALUE to differentiate it from the other parameters.
Here is what it should look like, assuming that you will modify your
procedure to return the value via the output parameter rather than the
return statement:

With cmd.Parameters
.Append cmd.CreateParameter("RETURN_VALUE", _
adInteger, adParamReturnValue)
set param =.CreateParameter ("@userid", adNumeric, _
adParamInput)
param.Precision=18
param.NumericScale = 0
param.value = 1
.Append param
set param =.CreateParameter ("@result", adNumeric, _
adParamOutput)
param.Precision=18
param.NumericScale = 0
.Append param
End With
Set objRS = cmd.Execute
Response.Write objRS.Fields("email").Value&"<br>"
objRS.close: Set objRS=Nothing
xx=cmd.Parameters ("@result").Value
Response.Write xx

I''ve written a free tool to generate the vbscript code needed to execute a
stored procedure. You may find it helpful. It''s available here:
http://www.thrasherwebdesign.com/ind...s&hp=links.asp

HTH,
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don''t check it very often. You will get a
quicker response by posting to the newsgroup.

CREATE PROCEDURE st_deneme @userid numeric（18），@ result numeric（18 ）输出

AS

设置NOCOUNT ON

select * from users

return" 10"

GO


/ ******************************* ****************

<！ - METADATA TYPE =" typelib"

NAME =" Microsoft ActiveX Data Objects 2.8 Library"

UUID =" {2A75196C-D9EB-4129-B803-931327F72D5C}"

VERSION =" 2.8" - >

<！ - #include file =" conn.asp" - >

<％

设置cmd = server。 CreateObject（" ADODB.Command"）

cmd.CommandType = 4

cmd.ActiveConnection = baglantim

cmd.CommandText =" st_deneme" ;


cmd.CreateParameter" @ userid"，adNumeric，adParamInput，100,100

cmd.CreateParameter" @result " ，adNumeric，adParamOutput，18,18

''cmd.CreateParameter" @ result"，adNumeric，adParamReturnValue，18,18

cmd.Parameters（" @ userid" ;）。值= 1

xx = cmd.Parameters（" @ result"）。值

设置objRS = cmd.Execute

Response.Write objRS.Fields（" email"）。Value&"< br>"

Response.Write xx

％>


它写* Response.Write objRS.Fields（" email"）。Value&"< br>" *（此行的值

）


但不是返回值？为什么？

CREATE PROCEDURE st_deneme @userid numeric (18) ,@result numeric (18) output
AS
SET NOCOUNT ON
select * from users
return "10"
GO

/***********************************************
<!--METADATA TYPE="typelib"
NAME="Microsoft ActiveX Data Objects 2.8 Library"
UUID="{2A75196C-D9EB-4129-B803-931327F72D5C}"
VERSION="2.8"-->
<!--#include file="conn.asp"-->
<%
Set cmd=server.CreateObject("ADODB.Command")
cmd.CommandType=4
cmd.ActiveConnection=baglantim
cmd.CommandText = "st_deneme"

cmd.CreateParameter "@userid", adNumeric, adParamInput,100 ,100
cmd.CreateParameter "@result" ,adNumeric ,adParamOutput ,18 , 18
''cmd.CreateParameter "@result",adNumeric ,adParamReturnValue ,18,18
cmd.Parameters ("@userid").Value =1
xx=cmd.Parameters ("@result").Value
Set objRS = cmd.Execute
Response.Write objRS.Fields("email").Value&"<br>"
Response.Write xx
%>

it writes *Response.Write objRS.Fields("email").Value&"<br>"* (the value
of this row)

but not return value? why?

这篇关于从sp（十字架）返回的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！