473,248 Members | 1,432 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,248 software developers and data experts.

stored procedure return value to ASP

JT
how can i see a stored procedures return value in ASP??
Jul 19 '05 #1
7 5697
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: C Kirby | last post by:
In SQL Server 2000, I've got a rather lengthy stored procedure, which creates a lot of temporary tables as it processes down through a few sets of data. When testing it through Query Analyzer, it...
0
by: Nashat Wanly | last post by:
HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and Visual C# .NET View products that this article applies to. This article was previously published under Q310070 For a Microsoft...
2
by: Dino L. | last post by:
How can I run stored procedure (MSSQL) ?
5
by: Sandy | last post by:
Hello - I need a good example of how to take a return value from a stored procedure and use it in vb code. I have an app that searches a database by city and state. If a user makes a typo, the...
7
by: Siv | last post by:
Hi, I have a stored procedure that I want to execute and then wait in a loop showing a timer whilst it completes and then carry on once I get notification that it has completed. The main reason...
4
by: nishi57 | last post by:
I hope I can get some help regarding this issue, which has been going on for a while. I have a desktop user who is having problem running "Stored Procedures". The DB2 Connect application works fine...
4
by: scparker | last post by:
Hello, We have a stored procedure that does a basic insert of values. I am then able to retrieve the ID number created for this new record. We are currently using ASP.NET 2.0 and use N-Tier...
9
by: fniles | last post by:
I am using VB.NET 2003 and SQL2000 database. I have a stored procedure called "INSERT_INTO_MYTABLE" that accepts 1 parameter (varchar(10)) and returns the identity column value from that table....
9
by: Frawls | last post by:
Hi I Am am having problems with a stored Procedure that i wrote. Basically whats happening is that the Stored procedure Runs fine when i EXECUTE it in SQL Query analyzer. But when i debug...
12
by: Dooza | last post by:
I have a stored procedure that takes a number of inputs, does a bulk insert, and then outputs a recordset. When I run the stored procedure in Server Management Studio I also get a return value from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.