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.Crea teObject("ADODB .Command")
cmd.CommandType =4
cmd.ActiveConne ction=baglantim
cmd.CommandText = "st_deneme"
cmd.CreateParam eter "@userid", adNumeric, adParamInput,10 0 ,100
cmd.CreateParam eter "@result" ,adNumeric ,adParamOutput ,18 , 18
'cmd.CreatePara meter "@result",adNum eric ,adParamReturnV alue ,18,18
cmd.Parameters ("@userid").Val ue =1
xx=cmd.Paramete rs ("@result").Val ue
Set objRS = cmd.Execute
Response.Write objRS.Fields("e mail").Value&"< br>"
Response.Write xx
%>

dont return any value .. what is the problem?

[Alejandro Mesa]

RE: returning from sp(cross)

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:
[color=blue]
> 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.Crea teObject("ADODB .Command")
> cmd.CommandType =4
> cmd.ActiveConne ction=baglantim
> cmd.CommandText = "st_deneme"
> cmd.CreateParam eter "@userid", adNumeric, adParamInput,10 0 ,100
> cmd.CreateParam eter "@result" ,adNumeric ,adParamOutput ,18 , 18
> 'cmd.CreatePara meter "@result",adNum eric ,adParamReturnV alue ,18,18
> cmd.Parameters ("@userid").Val ue =1
> xx=cmd.Paramete rs ("@result").Val ue
> Set objRS = cmd.Execute
> Response.Write objRS.Fields("e mail").Value&"< br>"
> Response.Write xx
> %>
>
> dont return any value .. what is the problem?
>
>
>
>
>
>[/color]

[Bob Barrows [MVP]]

Re: returning from sp(cross)

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:[color=blue]
>
> "Savas Ates" wrote:
>[color=green]
>> CREATE PROCEDURE st_deneme @userid numeric (18) ,[/color][/color]

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)
[color=blue][color=green]
>> @result numeric
>> (18) output AS
>> select * from users
>> return "10"
>> GO
>> *************** *************[/color][/color]

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&selm=OgYK94SgEHA.4092%40TK2MSFTNGP10.phx.gbl

[color=blue][color=green]
>>
>> <%
>> Set cmd=server.Crea teObject("ADODB .Command")
>> cmd.CommandType =4
>> cmd.ActiveConne ction=baglantim
>> cmd.CommandText = "st_deneme"
>> cmd.CreateParam eter "@userid", adNumeric, adParamInput,10 0 ,100[/color][/color]

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.

[color=blue][color=green]
>> cmd.CreateParam eter "@result" ,adNumeric ,adParamOutput ,18 , 18
>> 'cmd.CreatePara meter "@result",adNum eric ,adParamReturnV alue ,18,18[/color][/color]

In addition to creating the parameter objects, you have to append them to
the Parameters collection. See below.
[color=blue][color=green]
>> cmd.Parameters ("@userid").Val ue =1
>> xx=cmd.Paramete rs ("@result").Val ue[/color][/color]

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

[color=blue][color=green]
>> Set objRS = cmd.Execute
>> Response.Write objRS.Fields("e mail").Value&"< br>"[/color][/color]

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.)
[color=blue][color=green]
>> Response.Write xx
>> %>[/color][/color]


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.CreateParam eter("RETURN_VA LUE", _
adInteger, adParamReturnVa lue)
set param =.CreateParamet er ("@userid", adNumeric, _
adParamInput)
param.Precision =18
param.NumericSc ale = 0
param.value = 1
.Append param
set param =.CreateParamet er ("@result", adNumeric, _
adParamOutput)
param.Precision =18
param.NumericSc ale = 0
.Append param
End With
Set objRS = cmd.Execute
Response.Write objRS.Fields("e mail").Value&"< br>"
objRS.close: Set objRS=Nothing
xx=cmd.Paramete rs ("@result").Val ue
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
--
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.

[Savas Ates]

still doesnt return

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.Crea teObject("ADODB .Command")
cmd.CommandType =4
cmd.ActiveConne ction=baglantim
cmd.CommandText = "st_deneme"

cmd.CreateParam eter "@userid", adNumeric, adParamInput,10 0 ,100
cmd.CreateParam eter "@result" ,adNumeric ,adParamOutput ,18 , 18
'cmd.CreatePara meter "@result",adNum eric ,adParamReturnV alue ,18,18
cmd.Parameters ("@userid").Val ue =1
xx=cmd.Paramete rs ("@result").Val ue
Set objRS = cmd.Execute
Response.Write objRS.Fields("e mail").Value&"< br>"
Response.Write xx
%>

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

but not return value? why?

[Savas Ates]

doesnt work

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.Crea teObject("ADODB .Command")
cmd.CommandType =4
cmd.ActiveConne ction=baglantim
cmd.CommandText = "st_deneme"

With cmd.Parameters
.Append cmd.CreateParam eter("@result", _
adInteger, adParamReturnVa lue)
set param =cmd.CreatePara meter ("@userid", adNumeric, _
adParamInput)
param.Precision =18
param.NumericSc ale = 0
param.value = 1
.Append param
set param =cmd.CreatePara meter ("@result", adNumeric, _
adParamOutput)
param.Precision =18
param.NumericSc ale = 0
.Append param
End With
Set objRS = cmd.Execute
Response.Write objRS.Fields("e mail").Value&"< br>"
objRS.close: Set objRS=Nothing
xx=cmd.Paramete rs ("@result").Val ue
Response.Write xx
Response.End %>

[Aaron [SQL Server MVP]]

Re: doesnt work

What on earth does "doesnt work" mean?

--

http://www.aspfaq.com/

(Reverse address to reply.)




"Savas Ates" <savas@indexint eractive.com> wrote in message
news:eT6iuzPwEH A.3288@TK2MSFTN GP14.phx.gbl...[color=blue]
> 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.Crea teObject("ADODB .Command")
> cmd.CommandType =4
> cmd.ActiveConne ction=baglantim
> cmd.CommandText = "st_deneme"
>
> With cmd.Parameters
> .Append cmd.CreateParam eter("@result", _
> adInteger, adParamReturnVa lue)
> set param =cmd.CreatePara meter ("@userid", adNumeric, _
> adParamInput)
> param.Precision =18
> param.NumericSc ale = 0
> param.value = 1
> .Append param
> set param =cmd.CreatePara meter ("@result", adNumeric, _
> adParamOutput)
> param.Precision =18
> param.NumericSc ale = 0
> .Append param
> End With
> Set objRS = cmd.Execute
> Response.Write objRS.Fields("e mail").Value&"< br>"
> objRS.close: Set objRS=Nothing
> xx=cmd.Paramete rs ("@result").Val ue
> Response.Write xx
> Response.End %>
>
>[/color]

[Jeff Dillon]

Re: doesnt work

Does it work in Query Analyzer?

Jeff
"Savas Ates" <savas@indexint eractive.com> wrote in message
news:eT6iuzPwEH A.3288@TK2MSFTN GP14.phx.gbl...[color=blue]
> 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.Crea teObject("ADODB .Command")
> cmd.CommandType =4
> cmd.ActiveConne ction=baglantim
> cmd.CommandText = "st_deneme"
>
> With cmd.Parameters
> .Append cmd.CreateParam eter("@result", _
> adInteger, adParamReturnVa lue)
> set param =cmd.CreatePara meter ("@userid", adNumeric, _
> adParamInput)
> param.Precision =18
> param.NumericSc ale = 0
> param.value = 1
> .Append param
> set param =cmd.CreatePara meter ("@result", adNumeric, _
> adParamOutput)
> param.Precision =18
> param.NumericSc ale = 0
> .Append param
> End With
> Set objRS = cmd.Execute
> Response.Write objRS.Fields("e mail").Value&"< br>"
> objRS.close: Set objRS=Nothing
> xx=cmd.Paramete rs ("@result").Val ue
> Response.Write xx
> Response.End %>
>
>[/color]

[Savas Ates]

Re: returning from sp(cross)

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

but
Response.Write xx is null or nothing

[Ray Costanzo [MVP]]

Re: returning from sp(cross)

Perhaps you need to rearrange the order in which you're doing thing here:

xx=cmd.Paramete rs ("@result").Val ue ''before cmd.Execute?
Set objRS = cmd.Execute
Response.Write xx

Ray at home


"Savas Ates" <savas@indexint eractive.com> wrote in message
news:uVvZu5OwEH A.1400@TK2MSFTN GP11.phx.gbl...[color=blue]
> 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.Crea teObject("ADODB .Command")
> cmd.CommandType =4
> cmd.ActiveConne ction=baglantim
> cmd.CommandText = "st_deneme"
> cmd.CreateParam eter "@userid", adNumeric, adParamInput,10 0 ,100
> cmd.CreateParam eter "@result" ,adNumeric ,adParamOutput ,18 , 18
> 'cmd.CreatePara meter "@result",adNum eric ,adParamReturnV alue ,18,18
> cmd.Parameters ("@userid").Val ue =1
> xx=cmd.Paramete rs ("@result").Val ue
> Set objRS = cmd.Execute
> Response.Write objRS.Fields("e mail").Value&"< br>"
> Response.Write xx
> %>
>
> dont return any value .. what is the problem?
>
>
>
>
>[/color]

[Jeff Dillon]

Re: returning from sp(cross)

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" <savas@indexint eractive.com> wrote in message
news:uz0mKAQwEH A.4004@tk2msftn gp13.phx.gbl...[color=blue]
> Response.Write objRS.Fields("e mail").Value this value writes
>
> but
> Response.Write xx is null or nothing
>
>[/color]

[Alejandro Mesa]

RE: doesnt work

Savas,
[color=blue]
> 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[/color]

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:
[color=blue]
> 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.Crea teObject("ADODB .Command")
> cmd.CommandType =4
> cmd.ActiveConne ction=baglantim
> cmd.CommandText = "st_deneme"
>
> With cmd.Parameters
> .Append cmd.CreateParam eter("@result", _
> adInteger, adParamReturnVa lue)
> set param =cmd.CreatePara meter ("@userid", adNumeric, _
> adParamInput)
> param.Precision =18
> param.NumericSc ale = 0
> param.value = 1
> .Append param
> set param =cmd.CreatePara meter ("@result", adNumeric, _
> adParamOutput)
> param.Precision =18
> param.NumericSc ale = 0
> .Append param
> End With
> Set objRS = cmd.Execute
> Response.Write objRS.Fields("e mail").Value&"< br>"
> objRS.close: Set objRS=Nothing
> xx=cmd.Paramete rs ("@result").Val ue
> Response.Write xx
> Response.End %>
>
>
>[/color]

[Bob Barrows [MVP]]

Re: returning from sp(cross)

I'm reposting this because it appears that Savas never saw it.
[color=blue]
> "Savas Ates" wrote:
>[color=green]
>> CREATE PROCEDURE st_deneme @userid numeric (18) ,[/color][/color]

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)
[color=blue][color=green]
>> @result numeric
>> (18) output AS
>> select * from users
>> return "10"
>> GO
>> *************** *************[/color][/color]

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&selm=OgYK94SgEHA.4092%40TK2MSFTNGP10.phx.gbl

[color=blue][color=green]
>>
>> <%
>> Set cmd=server.Crea teObject("ADODB .Command")
>> cmd.CommandType =4
>> cmd.ActiveConne ction=baglantim
>> cmd.CommandText = "st_deneme"
>> cmd.CreateParam eter "@userid", adNumeric, adParamInput,10 0 ,100[/color][/color]

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.

[color=blue][color=green]
>> cmd.CreateParam eter "@result" ,adNumeric ,adParamOutput ,18 , 18
>> 'cmd.CreatePara meter "@result",adNum eric ,adParamReturnV alue ,18,18[/color][/color]

In addition to creating the parameter objects, you have to append them to
the Parameters collection. See below.
[color=blue][color=green]
>> cmd.Parameters ("@userid").Val ue =1
>> xx=cmd.Paramete rs ("@result").Val ue[/color][/color]

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

[color=blue][color=green]
>> Set objRS = cmd.Execute
>> Response.Write objRS.Fields("e mail").Value&"< br>"[/color][/color]

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.)
[color=blue][color=green]
>> Response.Write xx
>> %>[/color][/color]


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.CreateParam eter("RETURN_VA LUE", _
adInteger, adParamReturnVa lue)
set param =.CreateParamet er ("@userid", adNumeric, _
adParamInput)
param.Precision =18
param.NumericSc ale = 0
param.value = 1
.Append param
set param =.CreateParamet er ("@result", adNumeric, _
adParamOutput)
param.Precision =18
param.NumericSc ale = 0
.Append param
End With
Set objRS = cmd.Execute
Response.Write objRS.Fields("e mail").Value&"< br>"
objRS.close: Set objRS=Nothing
xx=cmd.Paramete rs ("@result").Val ue
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
--
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.