470,855 Members | 1,205 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,855 developers. It's quick & easy.

returning from sp(cross)

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?

Jul 21 '05 #1
11 2177
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:
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?


Jul 21 '05 #2
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" wrote:
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
%>

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.
Jul 21 '05 #3
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?


Jul 21 '05 #4
CREATE PROCEDURE st_deneme @userid numeric (18,0) ,@result numeric (18,0)
output AS
SET NOCOUNT ON
select * from users
return "10"
set @result="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"

With cmd.Parameters
.Append cmd.CreateParameter("@result", _
adInteger, adParamReturnValue)
set param =cmd.CreateParameter ("@userid", adNumeric, _
adParamInput)
param.Precision=18
param.NumericScale = 0
param.value = 1
.Append param
set param =cmd.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
Response.End %>
Jul 21 '05 #5
What on earth does "doesnt work" mean?

--
http://www.aspfaq.com/
(Reverse address to reply.)


"Savas Ates" <sa***@indexinteractive.com> wrote in message
news:eT**************@TK2MSFTNGP14.phx.gbl...
CREATE PROCEDURE st_deneme @userid numeric (18,0) ,@result numeric (18,0)
output AS
SET NOCOUNT ON
select * from users
return "10"
set @result="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"

With cmd.Parameters
.Append cmd.CreateParameter("@result", _
adInteger, adParamReturnValue)
set param =cmd.CreateParameter ("@userid", adNumeric, _
adParamInput)
param.Precision=18
param.NumericScale = 0
param.value = 1
.Append param
set param =cmd.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
Response.End %>

Jul 21 '05 #6
Does it work in Query Analyzer?

Jeff
"Savas Ates" <sa***@indexinteractive.com> wrote in message
news:eT**************@TK2MSFTNGP14.phx.gbl...
CREATE PROCEDURE st_deneme @userid numeric (18,0) ,@result numeric (18,0)
output AS
SET NOCOUNT ON
select * from users
return "10"
set @result="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"

With cmd.Parameters
.Append cmd.CreateParameter("@result", _
adInteger, adParamReturnValue)
set param =cmd.CreateParameter ("@userid", adNumeric, _
adParamInput)
param.Precision=18
param.NumericScale = 0
param.value = 1
.Append param
set param =cmd.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
Response.End %>

Jul 21 '05 #7
Response.Write objRS.Fields("email").Value this value writes

but
Response.Write xx is null or nothing
Jul 21 '05 #8
Perhaps you need to rearrange the order in which you're doing thing here:

xx=cmd.Parameters ("@result").Value ''before cmd.Execute?
Set objRS = cmd.Execute
Response.Write xx

Ray at home
"Savas Ates" <sa***@indexinteractive.com> wrote in message
news:uV**************@TK2MSFTNGP11.phx.gbl...
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?


Jul 21 '05 #9
Yes, you have to spin through the whole result set, before the output param
will become available.

While not objRS.EFO

objRS.MoveNext

wend

"Savas Ates" <sa***@indexinteractive.com> wrote in message
news:uz**************@tk2msftngp13.phx.gbl...
Response.Write objRS.Fields("email").Value this value writes

but
Response.Write xx is null or nothing

Jul 21 '05 #10
Savas,
CREATE PROCEDURE st_deneme @userid numeric (18,0) ,@result numeric (18,0)
output AS
SET NOCOUNT ON
select * from users
return "10"
set @result="10"
GO
Your sp will never reach the point where you set @result. The statement is
after the return. You should set it before the return.

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

AMB

"Savas Ates" wrote:
CREATE PROCEDURE st_deneme @userid numeric (18,0) ,@result numeric (18,0)
output AS
SET NOCOUNT ON
select * from users
return "10"
set @result="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"

With cmd.Parameters
.Append cmd.CreateParameter("@result", _
adInteger, adParamReturnValue)
set param =cmd.CreateParameter ("@userid", adNumeric, _
adParamInput)
param.Precision=18
param.NumericScale = 0
param.value = 1
.Append param
set param =cmd.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
Response.End %>

Jul 21 '05 #11
I'm reposting this because it appears that Savas never saw it.
"Savas Ates" wrote:
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
%>

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.

Jul 21 '05 #12

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by briforge | last post: by
3 posts views Thread by David Busby | last post: by
reply views Thread by George Durzi | last post: by
4 posts views Thread by __Stephen | last post: by
4 posts views Thread by =?Utf-8?B?a2lzaG9y?= | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.