473,836 Members | 2,285 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Calling Stored Procedures

Hi All

I was wondering if there is a way to call a stored procedure from inside
another stored procedure. So for example my first procedure will call a
second stored procedure which when executed will return one record and i
want to use this data in the calling stored procedure. Is this possible ?

Thanks in advance
Jul 20 '05
18 19489
Hi simon

Just one other quick question, this isnt really important but is there a way
to find out how many records have been returned in the stored procedure from
vb ? If i use the .recordcount function with the object it returns -1
regardless of how many records there may be.

Thanks
"Simon Hayes" <sq*@hayes.ch > wrote in message
news:40******** **@news.bluewin .ch...

"Jarrod Morrison" <ja*****@ihug.c om.au> wrote in message
news:bv******** **@lust.ihug.co .nz...
Hi Simon

Thanks for the link, it did explain what i was trying to do but im not sure
if im going about it the right way, ive posted below the procedure im

using
and it works correctly under sql query analyzer but not in VB, im assuming that this is because im using a temp table and then deleting the temp

table
afterwards. The reason im using a temp table is because there isnt always going to be just one record returned from some of the searches so im
inserting the records into a temp table then one by one adding them to the search string and deleting each record and finally deleting the table. Is there a better way that i should be doing this ? Thanks for all your help


<snip>

If you're getting the right results in QA, then you should be able to
retrieve them in VB - you'd need to explain what you mean by "not working"
when you run it from VB, and which client library you use. If it's ADO,

then one common piece of advice is to put SET NOCOUNT ON at the start of your
procedure:

http://www.aspfaq.com/show.asp?id=2246

Simon

Jul 20 '05 #11
Jarrod,
Look at CursorType and CursorLocation in ADO. You are probably using a
combination which does not give you the recordcount (and then ADO indicates
this by returning -1). ForwardOnly is the default CursorType and it does not
give you the recordcount...
--
Lars Broberg
Elbe-Data AB
http://www.elbe-data.se
Remove "nothing." when replying to private e-mail!
"Jarrod Morrison" <ja*****@ihug.c om.au> wrote in message
news:bv******** **@lust.ihug.co .nz...
Hi simon

Just one other quick question, this isnt really important but is there a way to find out how many records have been returned in the stored procedure from vb ? If i use the .recordcount function with the object it returns -1
regardless of how many records there may be.

Thanks
"Simon Hayes" <sq*@hayes.ch > wrote in message
news:40******** **@news.bluewin .ch...

"Jarrod Morrison" <ja*****@ihug.c om.au> wrote in message
news:bv******** **@lust.ihug.co .nz...
Hi Simon

Thanks for the link, it did explain what i was trying to do but im not

sure
if im going about it the right way, ive posted below the procedure im

using
and it works correctly under sql query analyzer but not in VB, im assuming that this is because im using a temp table and then deleting the temp

table
afterwards. The reason im using a temp table is because there isnt always going to be just one record returned from some of the searches so im
inserting the records into a temp table then one by one adding them to the search string and deleting each record and finally deleting the table. Is there a better way that i should be doing this ? Thanks for all your help


<snip>

If you're getting the right results in QA, then you should be able to
retrieve them in VB - you'd need to explain what you mean by "not working" when you run it from VB, and which client library you use. If it's ADO,

then
one common piece of advice is to put SET NOCOUNT ON at the start of your
procedure:

http://www.aspfaq.com/show.asp?id=2246

Simon


Jul 20 '05 #12
Hi Lars

Yes i am using the default cursor type in my vb code, which type of cursor
should i be using to return the record count ? Should i also be changing the
lock type as well ?

Thanks
"Lars Broberg" <la****@elbe-data.nothing.se > wrote in message
news:Ny******** ************@ne wsc.telia.net.. .
Jarrod,
Look at CursorType and CursorLocation in ADO. You are probably using a
combination which does not give you the recordcount (and then ADO indicates this by returning -1). ForwardOnly is the default CursorType and it does not give you the recordcount...
--
Lars Broberg
Elbe-Data AB
http://www.elbe-data.se
Remove "nothing." when replying to private e-mail!
"Jarrod Morrison" <ja*****@ihug.c om.au> wrote in message
news:bv******** **@lust.ihug.co .nz...
Hi simon

Just one other quick question, this isnt really important but is there a

way
to find out how many records have been returned in the stored procedure

from
vb ? If i use the .recordcount function with the object it returns -1
regardless of how many records there may be.

Thanks
"Simon Hayes" <sq*@hayes.ch > wrote in message
news:40******** **@news.bluewin .ch...

"Jarrod Morrison" <ja*****@ihug.c om.au> wrote in message
news:bv******** **@lust.ihug.co .nz...
> Hi Simon
>
> Thanks for the link, it did explain what i was trying to do but im not sure
> if im going about it the right way, ive posted below the procedure im using
> and it works correctly under sql query analyzer but not in VB, im

assuming
> that this is because im using a temp table and then deleting the temp table
> afterwards. The reason im using a temp table is because there isnt

always
> going to be just one record returned from some of the searches so im
> inserting the records into a temp table then one by one adding them to
the
> search string and deleting each record and finally deleting the
table.
Is
> there a better way that i should be doing this ? Thanks for all your

help
>

<snip>

If you're getting the right results in QA, then you should be able to
retrieve them in VB - you'd need to explain what you mean by "not working" when you run it from VB, and which client library you use. If it's

ADO, then
one common piece of advice is to put SET NOCOUNT ON at the start of

your procedure:

http://www.aspfaq.com/show.asp?id=2246

Simon



Jul 20 '05 #13
Jarrod Morrison (ja*****@ihug.c om.au) writes:
Yes i am using the default cursor type in my vb code, which type of
cursor should i be using to return the record count ? Should i also be
changing the lock type as well ?


In most cases you probably want a client-side cursor, but server-side
is the default. Set .CursorLocation to adUseClient. Then you only have
one cursor type to choose from, Static.

The reason you cannot get a record count with forward only, is that
you get the rows as soon as SQL Server finds them, so you have no idea
how many there will be until you're through.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #14
Jarrod,
As Erland said, but beware that you will get a "disconnect ed" recordset that
not (automatically) will reflect any changes done on the server. If you
shall change the lock type depends on your own application logic. How do you
update? If you do it by stored procedures your recordset can use
adLockReadOnly, but if you update via the recordset you need
adLockPessimist ic, adLockOptimisti c or adLockBatchOpti mistic.
--
Lars Broberg
Elbe-Data AB
http://www.elbe-data.se
Remove "nothing." when replying to private e-mail!d

"Erland Sommarskog" <so****@algonet .se> wrote in message
news:Xn******** **************@ 127.0.0.1...
Jarrod Morrison (ja*****@ihug.c om.au) writes:
Yes i am using the default cursor type in my vb code, which type of
cursor should i be using to return the record count ? Should i also be
changing the lock type as well ?


In most cases you probably want a client-side cursor, but server-side
is the default. Set .CursorLocation to adUseClient. Then you only have
one cursor type to choose from, Static.

The reason you cannot get a record count with forward only, is that
you get the rows as soon as SQL Server finds them, so you have no idea
how many there will be until you're through.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 20 '05 #15
Hi Erland

Thanks for the reply, the way you explained it sounds pretty straight
forward so i change the cursor type and location in my code but i still
recieve -1, could this be because the stored procedure has set nocount on ?
This is the code im using in the vb app

Dim PtrlCmd As New ADODB.Command

PtrlCmd.ActiveC onnection = CPDBase
PtrlCmd.Command Text = "sp_EntityMembe rShips"
PtrlCmd.Command Type = adCmdStoredProc
PtrlRst.CursorT ype = adOpenStatic
PtrlRst.CursorL ocation = adUseClient
PtrlCmd.Paramet ers.Append PtrlCmd.CreateP arameter("Machi neName", adVarChar,
adParamInput, 50, frmLoading.lblM achine)
PtrlCmd.Paramet ers.Append PtrlCmd.CreateP arameter("UserN ame", adVarChar,
adParamInput, 50, frmLoading.lblU serName)

Set PtrlRst = PtrlCmd.Execute

after this line i break and try to get the recordcount and still get a -1 ?

Thanks for your help

"Erland Sommarskog" <so****@algonet .se> wrote in message
news:Xn******** **************@ 127.0.0.1...
Jarrod Morrison (ja*****@ihug.c om.au) writes:
Yes i am using the default cursor type in my vb code, which type of
cursor should i be using to return the record count ? Should i also be
changing the lock type as well ?


In most cases you probably want a client-side cursor, but server-side
is the default. Set .CursorLocation to adUseClient. Then you only have
one cursor type to choose from, Static.

The reason you cannot get a record count with forward only, is that
you get the rows as soon as SQL Server finds them, so you have no idea
how many there will be until you're through.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 20 '05 #16
Jarrod Morrison (ja*****@ihug.c om.au) writes:
Thanks for the reply, the way you explained it sounds pretty straight
forward so i change the cursor type and location in my code but i still
recieve -1, could this be because the stored procedure has set nocount on ? This is the code im using in the vb app

Dim PtrlCmd As New ADODB.Command

PtrlCmd.ActiveC onnection = CPDBase
PtrlCmd.Command Text = "sp_EntityMembe rShips"
PtrlCmd.Command Type = adCmdStoredProc
PtrlRst.CursorT ype = adOpenStatic
PtrlRst.CursorL ocation = adUseClient


But since you are using cmd.Execute, you should set the cursor location
and cursor type on PtrlCmd. Setting the properties in PtrlRst is the
thing to do if you open the record set with rs.Open.

ADO is indeed very confusing...

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #17
Erland Sommarskog (so****@algonet .se) writes:
Jarrod Morrison (ja*****@ihug.c om.au) writes:
Thanks for the reply, the way you explained it sounds pretty straight
forward so i change the cursor type and location in my code but i still
recieve -1, could this be because the stored procedure has set nocount on

?
This is the code im using in the vb app

Dim PtrlCmd As New ADODB.Command

PtrlCmd.ActiveC onnection = CPDBase
PtrlCmd.Command Text = "sp_EntityMembe rShips"
PtrlCmd.Command Type = adCmdStoredProc
PtrlRst.CursorT ype = adOpenStatic
PtrlRst.CursorL ocation = adUseClient


But since you are using cmd.Execute, you should set the cursor location
and cursor type on PtrlCmd. Setting the properties in PtrlRst is the
thing to do if you open the record set with rs.Open.

ADO is indeed very confusing...


Indeed it is, and on top of that I am only an occasional ADO programmer,
which may explain my incorrect suggestions above.

You don't set the CursorLocation on the Command object; the place for
this is the Connection object. The CursorType property is only available
on the Recordset object, but the good news is that once you have gone
for client-side, there is only one cursor type available and that is
static.

Sorry for any confusion.

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #18
Hi Erland

Works a treat once i changed the cursor location for the connection. Thanks
once again for all your help and for explaining the reason why it wasnt
working

Thanks again

"Erland Sommarskog" <so****@algonet .se> wrote in message
news:Xn******** *************@1 27.0.0.1...
Erland Sommarskog (so****@algonet .se) writes:
Jarrod Morrison (ja*****@ihug.c om.au) writes:
Thanks for the reply, the way you explained it sounds pretty straight
forward so i change the cursor type and location in my code but i still
recieve -1, could this be because the stored procedure has set nocount
on ?
This is the code im using in the vb app

Dim PtrlCmd As New ADODB.Command

PtrlCmd.ActiveC onnection = CPDBase
PtrlCmd.Command Text = "sp_EntityMembe rShips"
PtrlCmd.Command Type = adCmdStoredProc
PtrlRst.CursorT ype = adOpenStatic
PtrlRst.CursorL ocation = adUseClient


But since you are using cmd.Execute, you should set the cursor location
and cursor type on PtrlCmd. Setting the properties in PtrlRst is the
thing to do if you open the record set with rs.Open.

ADO is indeed very confusing...


Indeed it is, and on top of that I am only an occasional ADO programmer,
which may explain my incorrect suggestions above.

You don't set the CursorLocation on the Command object; the place for
this is the Connection object. The CursorType property is only available
on the Recordset object, but the good news is that once you have gone
for client-side, there is only one cursor type available and that is
static.

Sorry for any confusion.

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 20 '05 #19

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

Similar topics

1
4383
by: Robert Scheer | last post by:
Hi. I wrote some stored procedures used by a web application. As I am fairly new to Oracle, I am missing some concepts when creating these procedures, as a result, the application is suffering from poor performance everytime I call these procedures. I would like your help in order to identify potential mistakes when coding these procedures and I am posting a package and a procedure I use very often on the system, as an example:
1
3143
by: ZeBerg | last post by:
Is it possible to have a stored procedure in database A while calling it from database B and have it manipulate the tables in database B (whatever the calling database happens to be)? We have a large-scale app that uses many complex stored procedures, and as of now, we're copying the SPs to every new database that is created, and it will soon become a nightmare for propagating updates and fixes. We'd like to keep a master set of the SPs...
1
2542
by: Lauren Quantrell | last post by:
I'm using an Access2K front end on a SQL Server2K backend. I use Scope_Identity() in a lot of stored procedures to pass the newwly inserted record's unique ID to the next select statement in the same stored procedure. What I'm wondering is how I can pass the Scope_Identity back to the calling application. I'm hoping someone can show me the SP code and the aceess code needed to accomplish the following: I insert a new record in a table...
3
2121
by: mdaetwyler | last post by:
Hi all I am trying to call a DB/2 v8.2 stored procedure from Perl DBI and am getting an error message telling me, that the routine could not be found in the library path. SQL0444N Routine "*_deposit" (specific name "SQL050519190148810") is implemented with code in library or path "\finban.cac_deposit", function "finban.cac_deposit" which cannot be accessed. Reason code: "4". SQLSTATE=42724
5
2645
by: Zlatko Matiæ | last post by:
Hello. How can I call some functions on MSDE when working in .mdb ? Especially in-line functions which are similar to stored procedures. How can I use MSDE in-line functions as recordsource for .mdb forms ? Can I call in-line functions using ADO ? I tried, but it seems that only stored procedures are allowed (adCmdStoredProc).... Thanks.
5
3489
by: Tim Marshall | last post by:
I was following the thread "Re: Access Treeview - Is it Safe Yet?" with interest and on reading the post describing Lauren Quantrell's SmartTree, I've run into something I don't understand: Stored Procedures. I thought stored pricedures were an Oracle/MS SQL Server thing and don't know how they work with Access Jet. I've looked at some of the help on stored procedures in A2003, but really don't understand what's going on. Can someone...
0
1971
by: JN | last post by:
Hello, I'm having problem calling stored procedures from Visual FoxPro database. I got the following exception error: "System.Data.OleDb.OleDbException: Unrecognized command verb" It seems like the stored procedures are not recognized by .Net.
2
1434
by: singlal | last post by:
Hi, my question was not getting any attention because it moved to 2nd page; so posting it again. Sorry for any inconvenience but I need to get it resolved fast. Need your help! **************************************************************************************************** Original Question: -------------------- Has anyone called a COBOL subroutine using COBOL CALL from a COBOL/DB2
4
6726
by: Jack | last post by:
Hi, I am trying to run an example code from a book. However I am getting the following error message: Number: -2147217900 Description: Syntax error or access violation Source: Microsoft OLE DB Provider for SQL Server SQLState: 42000
0
9810
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
10821
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...
0
10527
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10573
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
10241
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9358
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...
1
7773
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5812
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4001
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.