stored procedure return value to ASP

[JT]

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

[Peter Foti]

"JT" <[e-mail] m> wrote in message
news:e0******** ******@TK2MSFTN GP10.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

[Bob Barrows]

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"

[Aaron Bertrand [MVP]]

Also see
http://www.aspfaq.com/params.htm

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"JT" <[e-mail] m> wrote in message
news:e0******** ******@TK2MSFTN GP10.phx.gbl...

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

[Dave Anderson]

"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("Retu rnValue").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.

[Aaron Bertrand [MVP]]

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

[Dave Anderson]

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

[Aaron Bertrand [MVP]]

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**********@s pammotel.com> wrote in message
news:%2******** *******@TK2MSFT NGP10.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.