469,271 Members | 1,431 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

stored procedure return value to ASP

JT
how can i see a stored procedures return value in ASP??
Jul 19 '05 #1
7 5498
"JT" <jt@nospam.com> wrote in message
news:e0**************@TK2MSFTNGP10.phx.gbl...
how can i see a stored procedures return value in ASP??


Assign the results to a Recordset. Assuming you have a connection variable
dbConn, and your stored procedure is named sq_myStoredProc:

Set oRS = dbConn.Execute( "exec sq_myStoredProc" )

Regards,
Peter Foti
Jul 19 '05 #2
JT wrote:
how can i see a stored procedures return value in ASP??


Use a Command object. I've created a stored procedure code generator that is
available here:
http://www.thrasherwebdesign.com/ind...asp&c=&a=clear

After executing the Command, use

cmd.Parameters(0).value

to retrieve the Return value. Note, if your procedure also returns a
recordset, you have to retrieve the entire recordset (or close it) before
the return or output parameter values are accessible. Make sure you use SET
NOCOUNT ON in your stored procedure to avoid extra resultsets containing the
"x records affected" messages.

Although others will suggest using a recordset, I consider it to be highly
wasteful of sql server resources, network resources, and web server
resources to use a bulky recordset to retrieve a single value from your
database.

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"
Jul 19 '05 #3
Also see
http://www.aspfaq.com/params.htm

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"JT" <jt@nospam.com> wrote in message
news:e0**************@TK2MSFTNGP10.phx.gbl...
how can i see a stored procedures return value in ASP??

Jul 19 '05 #4
"JT" wrote:

how can i see a stored procedures return value in ASP??


I have found that one easy way is to forego RETURN in favor of SELECT in the
SP, as my colleagues have a greater comfort level with ADODB.Recordset
objects than with ADODB.Command ones.

When doing so, it is useful to wrap all but the output SELECT in a NOCOUNT
block:

CREATE PROCEDURE MySP ( ...parameters...) AS
SET NOCOUNT ON
{ perform insert/update as required }
SET NOCOUNT OFF
SELECT @@IDENTITY AS ReturnValue

You can then examine [rs.Fields("ReturnValue").Value] in your script.

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.
Jul 19 '05 #5
> When doing so, it is useful to wrap all but the output SELECT in a NOCOUNT
block:

CREATE PROCEDURE MySP ( ...parameters...) AS
SET NOCOUNT ON


Or, just always put SET NOCOUNT ON at the beginning of all procedures.
There is no reason to SET NOCOUNT OFF.

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
Jul 19 '05 #6
"Aaron Bertrand [MVP]" wrote:

Or, just always put SET NOCOUNT ON at the beginning of
all procedures. There is no reason to SET NOCOUNT OFF.


There is one reason, but maybe not for everyone. If you use Query Analyzer
to test/debug your procedures, the count information is occasionally useful.
--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.
Jul 19 '05 #7
I would rather manually add that in using @@ROWCOUNT during debugging, then
I can print custom messages along with it. For a long procedure, this isn't
particularly useful to me:

1 rows(s) affected.

14 rows(s) affected.

5 rows(s) affected.

12 rows(s) affected.

0 rows(s) affected.

10 rows(s) affected.

....because then I have to walk through my stored procedure and figure out
which count went with which statement. Note that one statement can produce
more than one count (e.g. insert into a table with a trigger).

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Dave Anderson" <GT**********@spammotel.com> wrote in message
news:%2***************@TK2MSFTNGP10.phx.gbl...
"Aaron Bertrand [MVP]" wrote:

Or, just always put SET NOCOUNT ON at the beginning of
all procedures. There is no reason to SET NOCOUNT OFF.
There is one reason, but maybe not for everyone. If you use Query Analyzer
to test/debug your procedures, the count information is occasionally

useful.

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use of this email address implies consent to these terms. Please do not contact me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.

Jul 19 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by C Kirby | last post: by
2 posts views Thread by Dino L. | last post: by
5 posts views Thread by Sandy | last post: by
4 posts views Thread by scparker | last post: by
9 posts views Thread by fniles | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.