473,881 Members | 1,725 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Return value from Stored Procedure

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 the stored
procedure which is an INT.

I want to access this return value on my ASP/VBScript page, but do not
know how to access it.

Here is my code so far:

<%
Dim rsImport
Dim rsImport_cmd
Dim rsImport_numRow s

Set rsImport_cmd = Server.CreateOb ject ("ADODB.Command ")
rsImport_cmd.Ac tiveConnection = MM_aclv4test_ST RING
rsImport_cmd.Co mmandText = "{call dbo.PriceUpdate Import(?,?,?,?, ?)}"
rsImport_cmd.Pr epared = true
rsImport_cmd.Pa rameters.Append
rsImport_cmd.Pa rameters.Append rsImport_cmd.Cr eateParameter(" param1",
200, 1, 255, rsImport__vendo r)
rsImport_cmd.Pa rameters.Append rsImport_cmd.Cr eateParameter(" param2",
200, 1, 255, rsImport__name)
rsImport_cmd.Pa rameters.Append rsImport_cmd.Cr eateParameter(" param3",
200, 1, 255, rsImport__filen ame)
rsImport_cmd.Pa rameters.Append rsImport_cmd.Cr eateParameter(" param4",
200, 1, 255, rsImport__valid from)
rsImport_cmd.Pa rameters.Append rsImport_cmd.Cr eateParameter(" param5",
200, 1, 255, rsImport__user)

Set rsImport = rsImport_cmd.Ex ecute
rsImport_numRow s = 0
%>

There is more code above that sets the variable for each parameter.

I have tried adding this: rsImport_cmd.Cr eateParameter(" @RETURN_VALUE",
3, 4) but I got this error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e21'

Multiple-step OLE DB operation generated errors. Check each OLE DB
status value, if available. No work was done.

My stored procedure doesn't declare an output return value, which may be
the problem, but when I execute the stored procedure in server
management studio I am given a return value.

Any ideas or pointers?

Cheers,

Steve
Sep 16 '08 #1
12 5148
Dooza wrote:
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 the stored
procedure which is an INT.

I want to access this return value on my ASP/VBScript page, but do not
know how to access it.

Here is my code so far:

<%
Dim rsImport
Dim rsImport_cmd
Dim rsImport_numRow s

Set rsImport_cmd = Server.CreateOb ject ("ADODB.Command ")
rsImport_cmd.Ac tiveConnection = MM_aclv4test_ST RING
rsImport_cmd.Co mmandText = "{call dbo.PriceUpdate Import(?,?,?,?, ?)}"
rsImport_cmd.Pr epared = true
rsImport_cmd.Pa rameters.Append
rsImport_cmd.Pa rameters.Append rsImport_cmd.Cr eateParameter(" param1",
200, 1, 255, rsImport__vendo r)
rsImport_cmd.Pa rameters.Append rsImport_cmd.Cr eateParameter(" param2",
200, 1, 255, rsImport__name)
rsImport_cmd.Pa rameters.Append rsImport_cmd.Cr eateParameter(" param3",
200, 1, 255, rsImport__filen ame)
rsImport_cmd.Pa rameters.Append rsImport_cmd.Cr eateParameter(" param4",
200, 1, 255, rsImport__valid from)
rsImport_cmd.Pa rameters.Append rsImport_cmd.Cr eateParameter(" param5",
200, 1, 255, rsImport__user)

Set rsImport = rsImport_cmd.Ex ecute
rsImport_numRow s = 0
%>

There is more code above that sets the variable for each parameter.

I have tried adding this:
rsImport_cmd.Cr eateParameter(" @RETURN_VALUE", 3, 4) but I got this
error:
The return parameter should be the first one you append. You will
probably appreciate my free Command object code generator available
here: http://common.mvps.org/barrowsb/Clas..._generator.zip
My stored procedure doesn't declare an output return value, which may
be the problem,
No. A peculiarity of SQL Server is that it will not send output or
return parameters until all resultsets generated by the procedure have
been consumed by the caller. This is done for you behind the scenes in
Query Manager. In ADO code, you have to do this yourself. You have to
either navigate to the last record in the recordset, or close the
recordset, before attempting to read the output or return parameter
values. Switching to a client-side cursor guarantees that the current
resultset is consumed.

Moreover, you need to make sure the procedure is not generating any
unexpected resultsets. Another peculiarity of SQL Server is that it
sends those "x rows were affected" messages to the caller as closed
resultsets. If your ADO code does not handle them, you will never see
the output or return parameters. So, your standard practice should be to
suppress those messages by including the line "SET NOCOUNT ON" at the
beginning of your procedure.

--
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.
Sep 16 '08 #2
Bob Barrows [MVP] wrote:
The return parameter should be the first one you append. You will
probably appreciate my free Command object code generator available
here: http://common.mvps.org/barrowsb/Clas..._generator.zip
Thanks Bob, I will give it a go.
>My stored procedure doesn't declare an output return value, which may
be the problem,
No. A peculiarity of SQL Server is that it will not send output or
return parameters until all resultsets generated by the procedure have
been consumed by the caller. This is done for you behind the scenes in
Query Manager. In ADO code, you have to do this yourself. You have to
either navigate to the last record in the recordset, or close the
recordset, before attempting to read the output or return parameter
values. Switching to a client-side cursor guarantees that the current
resultset is consumed.
Aha, the missing piece of the pie, that will explain it.
Moreover, you need to make sure the procedure is not generating any
unexpected resultsets. Another peculiarity of SQL Server is that it
sends those "x rows were affected" messages to the caller as closed
resultsets. If your ADO code does not handle them, you will never see
the output or return parameters. So, your standard practice should be to
suppress those messages by including the line "SET NOCOUNT ON" at the
beginning of your procedure.
Thats something I have always let Server Studio Manager do for me each
time I create an SP... I didn't know what it was to begin with, so
Googled it and realised how important it was.

Thanks for the advice, I will let you know how I get on.

Steve
Sep 17 '08 #3
Bob Barrows [MVP] wrote:
The return parameter should be the first one you append. You will
probably appreciate my free Command object code generator available
here: http://common.mvps.org/barrowsb/Clas..._generator.zip
Bob, just to let you know this doesn't do anything in Firefox, but it
does work in Internet Explorer.

Steve
Sep 17 '08 #4
Dooza wrote:
Bob Barrows [MVP] wrote:
>The return parameter should be the first one you append. You will
probably appreciate my free Command object code generator available
here:
http://common.mvps.org/barrowsb/Clas..._generator.zip

Bob, just to let you know this doesn't do anything in Firefox, but it
does work in Internet Explorer.
I knew that and was willing to live with it. After all, I created it for my
own use and I use IE at work. You are certainly free to rewrite it to make
it browser-independent.

--
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"
Sep 17 '08 #5
Bob Barrows [MVP] wrote:
Dooza wrote:
>Bob Barrows [MVP] wrote:
>>The return parameter should be the first one you append. You will
probably appreciate my free Command object code generator available
here:
http://common.mvps.org/barrowsb/Clas..._generator.zip
Bob, just to let you know this doesn't do anything in Firefox, but it
does work in Internet Explorer.
I knew that and was willing to live with it. After all, I created it for my
own use and I use IE at work. You are certainly free to rewrite it to make
it browser-independent.
No thanks, I can live with using IE now and then :)

Steve
Sep 17 '08 #6
Bob Barrows [MVP] wrote:
No. A peculiarity of SQL Server is that it will not send output or
return parameters until all resultsets generated by the procedure have
been consumed by the caller. This is done for you behind the scenes in
Query Manager. In ADO code, you have to do this yourself. You have to
either navigate to the last record in the recordset, or close the
recordset, before attempting to read the output or return parameter
values. Switching to a client-side cursor guarantees that the current
resultset is consumed.
I have decided to change my tactics, as I needed the return parameter
before the recordset.

The stored procedure does a bulk insert into a temporary table, does
some checks on the data. If there are duplicates it returns an error
code and a recordset with the duplicate data.

If there are no duplicates but other errors it will output different
error numbers.

If there are no errors, a success number is returned and the data is
inserted into a permanent table and its details inserted into a job
table, with its ID returned.

My ASP needs to check the error number and display a message. If the
duplicates are found it needs to list them. I need the error message
before the recordset.

I need to change my stored procedure to return 3 recordsets:

1. Error number
2. ID of import job if it was successful
3. Recordset of duplicates

Am I thinking this the right way?

Steve
Sep 17 '08 #7
Bob Barrows [MVP] wrote:
Dooza wrote:
>Bob Barrows [MVP] wrote:
Actually, from reading the next paragraphs, you don't. You need either a
recordset and error number, or a returned ID
>The stored procedure does a bulk insert into a temporary table, does
some checks on the data. If there are duplicates it returns an error
code and a recordset with the duplicate data.

If there are no duplicates but other errors it will output different
error numbers.

If there are no errors, a success number is returned and the data is
inserted into a permanent table and its details inserted into a job
table, with its ID returned.

I'm not clear here: if there are multiple records involved, why would there
necessarily be a single ID returned?
Just re-read this...

There are 3 tables
#Import - for the initial bulk insert from a tab separated file
PriceUpdateJob - the main details of the import, with an auto-id, name
of job, date of job, active from date, username, and various other bit
checks to indicate progress throughout the import process
PriceUpdateImpo rtData - for the actual data from the initial file, with
the job id and item id being primary keys.

Not sure if that makes much difference :)

Steve
Sep 17 '08 #8
Hi Bob,
I have changed my SP so that it doesn't specify an output parameter, I
am going to rely on the return value instead, if thats possible.

Throughout my SP I have RETURN 50001 to stop the SP and give the error
number. Right at the end, if everything has been successful I use RETURN
@import (which is the I job ID)

In SSMS I always get a recordset, even if its empty, and always get a
return value.

The return value is either 50000, 50001, 50002 or the ID of the job. I
may have to change the error numbers to something bigger, or do as you
suggest and have an output parameter.

I have also changed my code as per your instructions:

<%
Dim rsImport
Dim rsImport_cmd
Dim rsImport_numRow s

Set conn = CreateObject ("ADODB.Connect ion")
conn.open MM_aclv4test_ST RING
Set rsImport_cmd = CreateObject ("ADODB.Command ")
Set rsImport_cmd.Ac tiveConnection = conn
rsImport_cmd.Co mmandText = "PriceUpdateImp ort"
rsImport_cmd.Co mmandType = 4 'adCmdStoredPro c
rsImport_cmd.Pa rameters.Append rsImport_cmd.Cr eateParameter(" param1",
200, 1, 255, rsImport__vendo r) ' adVarChar
rsImport_cmd.Pa rameters.Append rsImport_cmd.Cr eateParameter(" param2",
200, 1, 255, rsImport__name) ' adVarChar
rsImport_cmd.Pa rameters.Append rsImport_cmd.Cr eateParameter(" param3",
200, 1, 255, rsImport__filen ame) ' adVarChar
rsImport_cmd.Pa rameters.Append rsImport_cmd.Cr eateParameter(" param4",
200, 1, 255, rsImport__valid from) ' adVarChar
rsImport_cmd.Pa rameters.Append rsImport_cmd.Cr eateParameter(" param5",
200, 1, 255, rsImport__user) ' adVarChar

Set rsImport = rsImport_cmd.Ex ecute

If NOT rsImport.EOF Then
ardata = rsImport.GetRow s
End if
rsImport.Close
errcode = rsImport_cmd(0)

conn.close
Set conn = Nothing
%>

When there are duplicates I can loop through the array of data and see
the duplicates, but I never get the correct errcode. I always get the
value of the first parameter. I have tried adding the @RETURN_VALUE
parameter but always get an error about there being too many arguments.

Am I making an obvious mistake?

Cheers,

Steve
Sep 17 '08 #9
Dooza wrote:
Hi Bob,
I have changed my SP so that it doesn't specify an output parameter, I
am going to rely on the return value instead, if thats possible.
It's possible, but not advisable.
>
Throughout my SP I have RETURN 50001 to stop the SP and give the error
number. Right at the end, if everything has been successful I use
RETURN @import (which is the I job ID)

In SSMS I always get a recordset, even if its empty, and always get a
return value.

The return value is either 50000, 50001, 50002 or the ID of the job. I
may have to change the error numbers to something bigger, or do as you
suggest and have an output parameter.
There should be no problem returning the
>
I have also changed my code as per your instructions:

<%
Dim rsImport
Dim rsImport_cmd
Dim rsImport_numRow s

Set conn = CreateObject ("ADODB.Connect ion")
conn.open MM_aclv4test_ST RING
Set rsImport_cmd = CreateObject ("ADODB.Command ")
Set rsImport_cmd.Ac tiveConnection = conn
rsImport_cmd.Co mmandText = "PriceUpdateImp ort"
rsImport_cmd.Co mmandType = 4 'adCmdStoredPro c
The Return parameter should have been appended here - this is very
important!
rsImport_cmd.Pa rameters.Append rsImport_cmd.Cr eateParameter(" param1",
200, 1, 255, rsImport__vendo r) ' adVarChar
rsImport_cmd.Pa rameters.Append rsImport_cmd.Cr eateParameter(" param2",
200, 1, 255, rsImport__name) ' adVarChar
rsImport_cmd.Pa rameters.Append rsImport_cmd.Cr eateParameter(" param3",
200, 1, 255, rsImport__filen ame) ' adVarChar
rsImport_cmd.Pa rameters.Append rsImport_cmd.Cr eateParameter(" param4",
200, 1, 255, rsImport__valid from) ' adVarChar
rsImport_cmd.Pa rameters.Append rsImport_cmd.Cr eateParameter(" param5",
200, 1, 255, rsImport__user) ' adVarChar

Set rsImport = rsImport_cmd.Ex ecute

If NOT rsImport.EOF Then
ardata = rsImport.GetRow s
End if
rsImport.Close
errcode = rsImport_cmd(0)
The 0 refers to the first item in the Parameters collection. Right now,
that is the rsImport__vendo r parameter. You need to append the
RETURN_VALUE parameter first!
>
conn.close
Set conn = Nothing
%>

When there are duplicates I can loop through the array of data and see
the duplicates, but I never get the correct errcode. I always get the
value of the first parameter. I have tried adding the @RETURN_VALUE
parameter but always get an error about there being too many
arguments.

Am I making an obvious mistake?
See above

--
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.
Sep 17 '08 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1700
by: Jeff Mason | last post by:
Consider the following (questionable, to be sure, but syntactally legal) stored procedure (using the Northwind database): Create Procedure Test As Return (Select Count(*) From Orders) Select * From Orders; If you execute this SP in QA via:
2
3417
by: Daniel | last post by:
hi ng, i am newbie to sqlserver and my problem seems simple, but i didn't find information about it: How can i display the RETURN @x value of a stored procedure in the sql analyzer of the sqlserver? thanks a lot, d
2
5194
by: Scott Natwick | last post by:
Hi, Is there a way to obtain the return code from a stored procedure? Here is what I have so far. The procedure executes, but I'm not able to find the return code from the procedure. Thanks, Scott
8
2166
by: Peter | last post by:
Hi, there I have created an stored procedure using the DDL below for my MS Access Database and no error occurs. Also it can create an stored procedure if I changed the parameter from "" to ""@zSampleName". OleDbcmd.CommandText = _ "CREATE PROCEDURE udpGetSampleIDByName" & vbCrLf & _ "( VarChar(64))" & vbCrLf & _ "AS" & vbCrLf & _
5
6863
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 info sent to the stored procedure makes the stored procedure fail. I want to be able to display a label indicating they should try again, if this is the case. I can't use row count, because the stored procedure doesn't get that far. What...
2
2685
by: philip | last post by:
hello, i am new to asp.net and sql server, and i have 3 questions for asking: 1. i am writing a store procedure of login validation for my asp.net application and wondering what the different between RETURN and SELECT is. if exists(select * from users where username = @username and password = @password) BEGIN
1
5663
by: psycho | last post by:
How do we return a single value from a stored procedure. Suppose I have a stored procedure like this: create proc dbo.spInsertGroup @ID uniqueidentifier @GroupName varchar(100), @IsActive bit AS BEGIN
4
2996
by: jleeie | last post by:
Can someone help me, I'm going round in circles with this and my head is cabbaged ! I am using visual studio 2005 & VB & MS SQL 2005 I am trying to execute a stored procedure from within a program. I want to return values. I can get a 0 or 1 returned. I don't seem to be able to get any other value returned. below is my VB code and the stored procedure. I would really appreciate if someone would have a look that knows how to do this....
0
9926
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9776
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11095
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10812
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9552
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7108
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
4597
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4194
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3223
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.