returning single value from stored procedure

I would like to have a stored procedure that returns the maxium value
given the value of two input parameters. I have written the following
code:
CREATE PROCEDURE ProcMaxResponse
@MemberNumber int,
@Action nvarchar(80),
@NextNumber int OUT
AS
SELECT MAX(fldResponse) AS fldResponse FROM tblProcessed
WHERE fldMemberNo = @MemberNumber and fldAction = @Action

Would someone please tell me where I go from here? The examples I have
seen in various newsgroup seem far more complex then needs to be.

Thanks
Charles

Re: returning single value from stored procedure

What type and version of database are you using? I am going to assume SQL
Server 2000. Correct me if i am wrong.

Charles wrote:
> I would like to have a stored procedure that returns the maxium value
> given the value of two input parameters. I have written the following
> code:
> CREATE PROCEDURE ProcMaxResponse
> @MemberNumber int,
> @Action nvarchar(80),
> @NextNumber int OUT
> AS
> SELECT MAX(fldResponse) AS fldResponse FROM tblProcessed
> WHERE fldMemberNo = @MemberNumber and fldAction = @Action
>
> Would someone please tell me where I go from here? The examples I
> have seen in various newsgroup seem far more complex then needs to be.
>
You are mixing the methods for returning values from stored procedures. Read
this:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.general/msg/2c935bd7c531d82b

OK? Clear now? Three methods: resultset, output parameter and return value.
Pick one.

For this task, I would choose output parameter. So modify your procedure
like so:

CREATE PROCEDURE ProcMaxResponse
@MemberNumber int,
@Action nvarchar(80),
@NextNumber int OUT
AS
SET NOCOUNT ON
SET @NextNumber =
(SELECT MAX(fldResponse) AS fldResponse FROM tblProcessed
WHERE fldMemberNo = @MemberNumber and fldAction = @Action)

The SET NOCOUNT ON statement is very important. It prevents the query engine
from generating the "x rows were affected" messages that get passed to the
client as resultsets. These can interfere with ADO's ability to acquire the
output and return parameter values. It's good technique to inclued this
statement in all procedures that you plan to execute via ADO.

Retrieving an output parameter from a stored procedure via ADO requires that
you execute the query using the Parameters collection of a Command object to
pass and retrieve the parameter values. Best practice is to create the
Parameters collection yourself, rather than using the Refresh method which
forces an extra trip to the database. First, see this article for
recommendations for getting the ADO constants defined in your ASP page:
http://www.aspfaq.com/show.asp?id=2112
Here is what the vbscript code looks like (air code - there may be typos):

dim cn, cmd, params,memnum, action, nextnum
'populate and validate the memnum and action variables
'then:
set cn=createobject("adodb.connection")
cn.open "provider=sqloledb; ..."
'http://www.aspfaq.com/show.asp?id=2126
set cmd=createobject("adodb.command")
with cmd
.CommandText="ProcMaxResponse"
.CommandType=adCmdStoredProc
set params=.Parameters
params.append .createparameter("RETURN_VALUE", adInteger, _
adParamReturnValue)
params.append .createparameter("@MemberNumber, adInteger, _
adParamInput,,memnum)
params.append .createparameter("@Action , adIVarWChar, _
adParamInput,80,action)
params.append .createparameter("@NextNumber , adIVarWChar, _
adParamOutput)
.execute ,,adExecuteNoRecords
end with
nextnum=params("@NextNumber")
set cmd=nothing
cn.close:set cn=nothing
'continue


Yes, this looks like a lot of code, but it runs very efficiently. A drawback
is that it is easy to make a mistake while writing the code. Using a code
generator can help prevent that. I wrote one that is available here:
http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links .asp&c=&a=clear

Some people advocate always using resultsets to retrieve values from
procedures, and never using an explicit command object. See
http://tinyurl.com/jyy0 for a link to the article explaining that point of
view as well as my rationale for encouraging the use of the command object.


Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: returning single value from stored procedure

Bob Barrows [MVP] wrote:
> What type and version of database are you using? I am going to assume SQL
> Server 2000. Correct me if i am wrong.
>
> Charles wrote:
> > I would like to have a stored procedure that returns the maxium value
> > given the value of two input parameters. I have written the following
> > code:
> > CREATE PROCEDURE ProcMaxResponse
> > @MemberNumber int,
> > @Action nvarchar(80),
> > @NextNumber int OUT
> > AS
> > SELECT MAX(fldResponse) AS fldResponse FROM tblProcessed
> > WHERE fldMemberNo = @MemberNumber and fldAction = @Action
> >
> > Would someone please tell me where I go from here? The examples I
> > have seen in various newsgroup seem far more complex then needs to be.
> >
> You are mixing the methods for returning values from stored procedures. Read
> this:
> http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.general/msg/2c935bd7c531d82b
>
> OK? Clear now? Three methods: resultset, output parameter and return value.
> Pick one.
>
> For this task, I would choose output parameter. So modify your procedure
> like so:
>
> CREATE PROCEDURE ProcMaxResponse
> @MemberNumber int,
> @Action nvarchar(80),
> @NextNumber int OUT
> AS
> SET NOCOUNT ON
> SET @NextNumber =
> (SELECT MAX(fldResponse) AS fldResponse FROM tblProcessed
> WHERE fldMemberNo = @MemberNumber and fldAction = @Action)
>
> The SET NOCOUNT ON statement is very important. It prevents the query engine
> from generating the "x rows were affected" messages that get passed to the
> client as resultsets. These can interfere with ADO's ability to acquire the
> output and return parameter values. It's good technique to inclued this
> statement in all procedures that you plan to execute via ADO.
>
> Retrieving an output parameter from a stored procedure via ADO requires that
> you execute the query using the Parameters collection of a Command object to
> pass and retrieve the parameter values. Best practice is to create the
> Parameters collection yourself, rather than using the Refresh method which
> forces an extra trip to the database. First, see this article for
> recommendations for getting the ADO constants defined in your ASP page:
> http://www.aspfaq.com/show.asp?id=2112
> Here is what the vbscript code looks like (air code - there may be typos):
>
> dim cn, cmd, params,memnum, action, nextnum
> 'populate and validate the memnum and action variables
> 'then:
> set cn=createobject("adodb.connection")
> cn.open "provider=sqloledb; ..."
> 'http://www.aspfaq.com/show.asp?id=2126
> set cmd=createobject("adodb.command")
> with cmd
> .CommandText="ProcMaxResponse"
> .CommandType=adCmdStoredProc
> set params=.Parameters
> params.append .createparameter("RETURN_VALUE", adInteger, _
> adParamReturnValue)
> params.append .createparameter("@MemberNumber, adInteger, _
> adParamInput,,memnum)
> params.append .createparameter("@Action , adIVarWChar, _
> adParamInput,80,action)
> params.append .createparameter("@NextNumber , adIVarWChar, _
> adParamOutput)
> .execute ,,adExecuteNoRecords
> end with
> nextnum=params("@NextNumber")
> set cmd=nothing
> cn.close:set cn=nothing
> 'continue
>
>
> Yes, this looks like a lot of code, but it runs very efficiently. A drawback
> is that it is easy to make a mistake while writing the code. Using a code
> generator can help prevent that. I wrote one that is available here:
> http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links .asp&c=&a=clear
>
> Some people advocate always using resultsets to retrieve values from
> procedures, and never using an explicit command object. See
> http://tinyurl.com/jyy0 for a link to the article explaining that point of
> view as well as my rationale for encouraging the use of the command object.
>
>
> Bob Barrows
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"


Bob,

I realized after posting I left out SQL 2000 and that I am using
VB.Net. This looks great and I really appreciate the quick response.
I don't mind writting alot of code, just want to make sure it is
necsarry. I'll give your response a try. Thanks

Charles

Re: returning single value from stored procedure

Bob,

I realized after I posted the message that I left out I was running SQL
2000 and programing in VB.NET 2003. I appreciate your quick response
and clarifying things for me. I tried to piece together code samples
from various sources. I don't mind writing alot of code if that what
needs to be done. What I see makes sense. I'll give it a try and
respond yea or 'help'. I will also look at your code generator.

Thanks
Charles

Re: returning single value from stored procedure

Charles wrote:
> Bob,
>
> I realized after I posted the message that I left out I was running
> SQL 2000 and programing in VB.NET 2003. I appreciate your quick
> response and clarifying things for me. I tried to piece together
> code samples from various sources. I don't mind writing alot of code
> if that what needs to be done. What I see makes sense. I'll give it
> a try and respond yea or 'help'. I will also look at your code
> generator.
>

The code I posted is vbscript code, not vb.net. Neither is the code
generator relevant for your purposes.

There was no way for you to know it, but this is a classic asp newsgroup.
While you may be lucky enough to find a dotnet-savvy person here who can
answer your question, you can eliminate the luck factor by posting your
question to a newsgroup where the dotnet-savvy people hang out. I suggest
microsoft.public.dotnet.framework.aspnet.

The vb.net code will be similar to the vbscript code, in that you will use a
SQLCommand object and append parameters to its Parameters collection prior
to executing it and reading the value of the output parameter.

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

Re: returning single value from stored procedure

Charles wrote:
> Bob,
>
> I realized after I posted the message that I left out I was running
> SQL 2000 and programing in VB.NET 2003. I appreciate your quick
> response and clarifying things for me. I tried to piece together
> code samples from various sources. I don't mind writing alot of code
> if that what needs to be done. What I see makes sense. I'll give it
> a try and respond yea or 'help'. I will also look at your code
> generator.
>
I also wanted to respond to your overall purpose which seems to be to
acquire a next number to be used for something. You may wish to rethink
this. In a multiuser environment it will be possible for two users to
retrieve the same next number. If you wish information about alternatives,
get back to us.

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

Re: returning single value from stored procedure

Bob,

Sorry about the double reply earlier, I thought I lost what I type the
first time.

Thanks for going further with this and offering help on the over all
purpose. First, I still need help. Since I am running VB.Net 2003,
the code snippit you supplied does not work (not your fault). Can you
apply that to .NET? I translated some of your code as:
With cmd
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@MemberNumber", intMemberNo)
.Parameters.Add("@Action", strAction)
.Parameters.Add("@NextNumber", SqlDbType.Int).Direction =
ParameterDirection.Output
End With
cn.Open()

Dim dr As SqlDataReader =
cmd.ExecuteReader(CommandBehavior.SingleRow)
dr.Read()
dr.Close()
nextnum = cmd.Parameters("@NextNumber")
I've tried seveal variations but @NextNumber keeps coming up null.

What I have is an application that tracks x number of responses to a
particular action. Based off other criteria, you can only have so many
response per action. I have three types of actions and only one user
doing data entry on any given action. Make Sense?
I did have this routine working in VB5 & VB6, but the SQL Statement was
in the app. I am now trying to improve my skills and use Stored
Procedures. Returning Recordsets, doing Inserts and deletes I have no
problem with.

Thanks again
Charles

Re: returning single value from stored procedure

You really need to move this to the aspnet group where you will get more
responses. However, read on:
Charles wrote:
> Bob,
>
> Sorry about the double reply earlier, I thought I lost what I type the
> first time.
>
> Thanks for going further with this and offering help on the over all
> purpose. First, I still need help. Since I am running VB.Net 2003,
> the code snippit you supplied does not work (not your fault). Can you
> apply that to .NET? I translated some of your code as:
> With cmd
> .CommandType = CommandType.StoredProcedure
> .Parameters.Add("@MemberNumber", intMemberNo)
> .Parameters.Add("@Action", strAction)
> .Parameters.Add("@NextNumber", SqlDbType.Int).Direction =
> ParameterDirection.Output
> End With
> cn.Open()
>
> Dim dr As SqlDataReader =
> cmd.ExecuteReader(CommandBehavior.SingleRow)

Since the query returns no records, don't use a datareader. Use
cmd.ExecuteNonQuery() instead.

(I'm assuming you modified your stored procedure to not return a resultset)

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.

Re: returning single value from stored procedure

"Charles" wrote in message
news:1131030760.447294.131680@o13g2000cwo.googlegroups.com.. .
> Bob,
>
> Sorry about the double reply earlier, I thought I lost what I type the
> first time.
>
> Thanks for going further with this and offering help on the over all
> purpose. First, I still need help. Since I am running VB.Net 2003,
> the code snippit you supplied does not work (not your fault). Can you
> apply that to .NET? I translated some of your code as:
> With cmd
> .CommandType = CommandType.StoredProcedure
> .Parameters.Add("@MemberNumber", intMemberNo)
> .Parameters.Add("@Action", strAction)
> .Parameters.Add("@NextNumber", SqlDbType.Int).Direction =
> ParameterDirection.Output

Does this actually work? Personally, I would do something like:
.Parameters.Add("@NextNumber", SqlDbType.Int)
.Parameters("@NextNumber").Direction = ParameterDirection.Output


> End With
> cn.Open()
>
> Dim dr As SqlDataReader =
> cmd.ExecuteReader(CommandBehavior.SingleRow)

You could probably get away with cmd.ExecuteNonQuery() and remove all the
data reader stuff.

@@IDENTITY isn't necessarily a good idea though - check out
http://www.experts-exchange.com/Programming/Programming_Lang uages/Dot_Net/VB_DOT_NET/Q_21588870.html
(delete cookies and refresh if it asks you to sign in and you don't have an
account - little bug I just noticed :)

> dr.Read()
> dr.Close()
> nextnum = cmd.Parameters("@NextNumber")
> I've tried seveal variations but @NextNumber keeps coming up null.
>
> What I have is an application that tracks x number of responses to a
> particular action. Based off other criteria, you can only have so many
> response per action. I have three types of actions and only one user
> doing data entry on any given action. Make Sense?
> I did have this routine working in VB5 & VB6, but the SQL Statement was
> in the app. I am now trying to improve my skills and use Stored
> Procedures. Returning Recordsets, doing Inserts and deletes I have no
> problem with.
>
> Thanks again
> Charles
>

Re: returning single value from stored procedure

Jevon wrote:
> "Charles" wrote in message
>> .Parameters.Add("@NextNumber", SqlDbType.Int).Direction =
>> ParameterDirection.Output
>
> Does this actually work?

I think so. I routinely do this:
..Parameters.Add("@Lifters", SqlDbType.SmallInt).Value = args.Lifters

If it works for the Value property, I assume it would also work for the
Direction property.

> @@IDENTITY isn't necessarily a good idea though - check out

Where did you see anything about @@IDENTITY? :-)

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

Re: returning single value from stored procedure

I tried cmd.ExecuteNonQuery() originally and it returned a value of
'nothing'. Everything else returns db.null

I will try the the aspnet group.

Thanks

Re: returning single value from stored procedure

>> @@IDENTITY isn't necessarily a good idea though - check out
>
> Where did you see anything about @@IDENTITY? :-)
Whoops! Mis-remembered the original posts. Doh!

Re: returning single value from stored procedure

I would like to thank everyone for the help. The problem turnned out
to be in the store procedure - @NextNumber int OUT should be
@NextNumber int OUTPUT.

Charles