returning from sp(cross)

[Savas Ates]

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?

 

[Alejandro Mesa]

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

 

[Bob Barrows [MVP]]

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

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)

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&lr=&c2coff=1&[email protected]


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.


In addition to creating the parameter objects, you have to append them to
the Parameters collection. See below.

This is too soon to try and get a value from your output parameter. You
haven't even executed the procedure yet!


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


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/index.asp?pi=links&hp=links.asp

HTH,
Bob Barrows

 

[Savas Ates]

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?

 

[Savas Ates]

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 %>

 

[Aaron [SQL Server MVP]]

What on earth does "doesnt work" mean?

 

[Jeff Dillon]

Does it work in Query Analyzer?

Jeff

 

[Savas Ates]

Response.Write objRS.Fields("email").Value this value writes

but
Response.Write xx is null or nothing

 

[Ray Costanzo [MVP]]

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

 

[Jeff Dillon]

Yes, you have to spin through the whole result set, before the output param
will become available.

While not objRS.EFO

objRS.MoveNext

wend

 

[Alejandro Mesa]

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

 

[Bob Barrows [MVP]]

I'm reposting this because it appears that Savas never saw it.

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)

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&lr=&c2coff=1&[email protected]


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.


In addition to creating the parameter objects, you have to append them to
the Parameters collection. See below.

This is too soon to try and get a value from your output parameter. You
haven't even executed the procedure yet!


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


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/index.asp?pi=links&hp=links.asp

HTH,
Bob Barrows