473,883 Members | 1,649 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 #1
18 19495
"Jarrod Morrison" <ja*****@ihug.c om.au> wrote in message news:<bv******* ***@lust.ihug.c o.nz>...
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


There are several options - see here:

http://www.sommarskog.se/share_data.html

If by "one record" you mean a scalar value, then an OUTPUT parameter
would work; if you mean a result set of one row, then you would need
one of the other approaches.

Simon
Jul 20 '05 #2
Jarrod,
There are 2 ways to do this. (probably more, but these are the 2 most
common ways). Both of these use the Northwind database, so you can test
yourself, if needed:

WAY 1 (this is my favorite because it lets you return multiple values):

create procedure sp_test1
as begin
select top 1 orderID from orders
where customerID = 'tomsp'
end

create procedure sp_test2
as begin
declare @my_value varchar(20)
exec @my_value = sp_test1
print @my_value
end

exec sp_test2
WAY 2 (this is probably more common, but the syntax is a little strange.
Note BOTH places where the keyword OUTPUT is used. Both are necessary):

create procedure sp_test1a @@outparam varchar(20) OUTPUT
as begin
select top 1 @@outparam = orderID from orders
where customerID = 'tomsp'
end

create procedure sp_test2a
as begin
declare @my_value varchar(20)
exec sp_test1a @my_value OUTPUT
print @my_value
end

exec sp_test2a

You can find these and many more questions answered at
www.TechnicalVideos.net
Best regards,
Chuck Conover
www.TechnicalVideos.net
"Jarrod Morrison" <ja*****@ihug.c om.au> wrote in message
news:bv******** **@lust.ihug.co .nz...
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 #3
Chuck Conover (cc******@comms peed.net) writes:
create procedure sp_test1
Don't your technical videos tell people to stay away from the sp_
prefix? This prefix is reserved from system procedures, and SQL Server
first looks for these in master. There is a slight performance penalty,
and if MS ships a new system procedure, you might be in for a surprise.
as begin
select top 1 orderID from orders
where customerID = 'tomsp'
end

create procedure sp_test2
as begin
declare @my_value varchar(20)
exec @my_value = sp_test1
print @my_value
end


I don't know what is supposed to look like, but it won't fly. sp_test1
does not have a RETURN statement, so it will always return 0. sp_test1
will also produce a result set, which will go to the client. In sp_test2
you are receiving the return value in a varchar(20), but the return
value from a stored procedure is an integer value.
--
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 #4
On Mon, 2 Feb 2004 23:13:55 +0000 (UTC) in
comp.databases. ms-sqlserver, Erland Sommarskog <so****@algonet .se>
wrote:
Chuck Conover (cc******@comms peed.net) writes:
create procedure sp_test1


Don't your technical videos tell people to stay away from the sp_
prefix? This prefix is reserved from system procedures, and SQL Server
first looks for these in master. There is a slight performance penalty,
and if MS ships a new system procedure, you might be in for a surprise.


On that note, is it good/bad practice (or even possible, I haven't
tried) to write some sp_whatever procedures and dump them into master?
Or should one create a common database for that stuff and call it like
exec common..sp_mypr oc, I get visions of invalid table name messages
if putting sps into a common database that would probably have no
tables.

--
A)bort, R)etry, I)nfluence with large hammer.
Jul 20 '05 #5
Trevor Best (bouncer@localh ost) writes:
On that note, is it good/bad practice (or even possible, I haven't
tried) to write some sp_whatever procedures and dump them into master?
Or should one create a common database for that stuff and call it like
exec common..sp_mypr oc, I get visions of invalid table name messages
if putting sps into a common database that would probably have no
tables.


And there those days when the manuals, at least those from Sybase,
almost encouraged people to write their own system procedures.

But those says are long gone by. Today, writing and installing your
own system procedures is not supported.

There are sometimes questions in the newsgroups on how to have stored
procedures in a common database, but these questions typically relate
to applications where you have multiple copies of the schema, and the
answer to these questions is that they need to learn release management.
--
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 #6
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
---- Stored Procedure Code -------

/*
** Determine Entity Launcher Items
*/

CREATE PROCEDURE [dbo].[EntityLauncherI tems]

@UserName VarChar(50),
@MachineName VarChar (50),
@EntityLocation ID VarChar(3)

AS

DECLARE @SqlStr VarChar(500) /* SQL Search String */
DECLARE @SrchInt VarChar(3) /* Search Integer */
DECLARE @IdCount Int /* ID Count */

SET @SrchInt = '1'

/* SELECT Public Items */

SET @SqlStr = 'SELECT AppID, Path, Name FROM Launcher_Items WHERE IsPub =
''' + '1' + ''''

/* Create Temporary Application ID Table */

CREATE TABLE #Id (AppID VarChar(4))

/* SELECT Single Machine Items */

INSERT INTO #Id (AppId) SELECT AppID FROM Launcher_Machin eAssoc WHERE
MachineName = @MachineName

/* SELECT Group Machine Items */

INSERT INTO #Id (AppId) SELECT AppID FROM Launcher_Locati onAssoc WHERE
LocationID = @EntityLocation Id

/* SELECT UserName Items */

INSERT INTO #Id (AppId) SELECT AppId FROM Launcher_UserAs soc WHERE
UserName = @UserName

/* Combine Non Public Applications Into Sql Search String */

SET @IdCount = (SELECT COUNT(AppId) FROM #Id)

WHILE @SrchInt <= @IdCount

BEGIN

IF @SrchInt = 1

BEGIN
SET @SqlStr = @SqlStr + ' UNION SELECT AppId, Path, Name FROM
Launcher_Items WHERE AppId = ''' + (SELECT TOP 1 AppId FROM #Id) + ''''
DELETE #Id FROM (SELECT TOP 1 * FROM #Id) AS t1 WHERE #Id.AppId =
t1.AppID
END

IF @SrchInt > 1

BEGIN
SET @SqlStr = @SqlStr + ' OR AppID = ''' + (SELECT TOP 1 AppId FROM
#Id) + ''''
DELETE #Id FROM (SELECT TOP 1 * FROM #Id) AS t1 WHERE #Id.AppId =
t1.AppID
END

SET @SrchInt = @SrchInt + 1

END

DROP TABLE #Id

EXEC (@SqlStr)
GO

"Simon Hayes" <sq*@hayes.ch > wrote in message
news:60******** *************** ***@posting.goo gle.com...
"Jarrod Morrison" <ja*****@ihug.c om.au> wrote in message

news:<bv******* ***@lust.ihug.c o.nz>...
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


There are several options - see here:

http://www.sommarskog.se/share_data.html

If by "one record" you mean a scalar value, then an OUTPUT parameter
would work; if you mean a result set of one row, then you would need
one of the other approaches.

Simon

Jul 20 '05 #7

"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 #8
Jarrod Morrison (ja*****@ihug.c om.au) writes:
WHILE @SrchInt <= @IdCount

BEGIN

IF @SrchInt = 1

BEGIN
SET @SqlStr = @SqlStr + ' UNION SELECT AppId, Path, Name FROM
Launcher_Items WHERE AppId = ''' + (SELECT TOP 1 AppId FROM #Id) + ''''
DELETE #Id FROM (SELECT TOP 1 * FROM #Id) AS t1 WHERE #Id.AppId =
t1.AppID
END

IF @SrchInt > 1

BEGIN
SET @SqlStr = @SqlStr + ' OR AppID = ''' + (SELECT TOP 1 AppId FROM
#Id) + ''''
DELETE #Id FROM (SELECT TOP 1 * FROM #Id) AS t1 WHERE #Id.AppId =
t1.AppID
END

SET @SrchInt = @SrchInt + 1

END


I might be missing something here, but why the dynamic SQL?

Why can't you just say:

SELECT AppID, Path, Name FROM Launcher_Items WHERE IsPub = '1'
UNION
SELECT AppID, Path, Name
FROM Launcher_Items l
WHERE EXISTS (SELECT *
FROM #Id i
WHERE l.AppId = i.AppId)
--
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 #9
Hey Simon

Your a champ, thanyou, it fixed it straight away. To answer your question
about VB when i tried to look at the data in the recordset i get an EOF
message. But after putting the SET NOCOUNT ON it fixed that straight away.
Thanks again for you help
"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 #10

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

Similar topics

1
4385
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
3144
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
2544
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
2124
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
2647
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
3492
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
1972
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
6727
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
9932
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
10730
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
10833
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
10405
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
9559
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
7114
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();...
0
5784
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5980
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
3228
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.