Calling Stored Procedures

[Jarrod Morrison]

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

[Simon Hayes]

Re: Calling Stored Procedures

"Jarrod Morrison" <jarrodm@ihug.c om.au> wrote in message news:<bvl7me$4t f$1@lust.ihug.c o.nz>...[color=blue]
> 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[/color]

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

[Chuck Conover]

Re: Calling Stored Procedures

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

https://www.TechnicalVideos.net

Best regards,
Chuck Conover

https://www.TechnicalVideos.net

"Jarrod Morrison" <jarrodm@ihug.c om.au> wrote in message
news:bvl7me$4tf $1@lust.ihug.co .nz...[color=blue]
> 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
>
>[/color]

[Erland Sommarskog]

Re: Calling Stored Procedures

Chuck Conover (cconover@comms peed.net) writes:[color=blue]
> create procedure sp_test1[/color]

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.
[color=blue]
> 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[/color]

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, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Trevor Best]

Re: Calling Stored Procedures

On Mon, 2 Feb 2004 23:13:55 +0000 (UTC) in
comp.databases. ms-sqlserver, Erland Sommarskog <sommar@algonet .se>
wrote:
[color=blue]
>Chuck Conover (cconover@comms peed.net) writes:[color=green]
>> create procedure sp_test1[/color]
>
>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.[/color]

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.

[Erland Sommarskog]

Re: Calling Stored Procedures

Trevor Best (bouncer@localh ost) writes:[color=blue]
> 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.[/color]

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, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Jarrod Morrison]

Re: Calling Stored Procedures

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" <sql@hayes.ch > wrote in message
news:60cd0137.0 402020643.520ad 289@posting.goo gle.com...[color=blue]
> "Jarrod Morrison" <jarrodm@ihug.c om.au> wrote in message[/color]
news:<bvl7me$4t f$1@lust.ihug.c o.nz>...[color=blue][color=green]
> > 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[/color][/color]
?[color=blue][color=green]
> >
> > Thanks in advance[/color]
>
> 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[/color]

[Simon Hayes]

Re: Calling Stored Procedures


"Jarrod Morrison" <jarrodm@ihug.c om.au> wrote in message
news:bvqfpj$c3p $1@lust.ihug.co .nz...[color=blue]
> Hi Simon
>
> Thanks for the link, it did explain what i was trying to do but im not[/color]
sure[color=blue]
> if im going about it the right way, ive posted below the procedure im[/color]
using[color=blue]
> 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[/color]
table[color=blue]
> 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
>[/color]

<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

[Erland Sommarskog]

Re: Calling Stored Procedures

Jarrod Morrison (jarrodm@ihug.c om.au) writes:[color=blue]
> 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[/color]

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, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Jarrod Morrison]

Re: Calling Stored Procedures

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" <sql@hayes.ch > wrote in message
news:40213aa5$1 _1@news.bluewin .ch...[color=blue]
>
> "Jarrod Morrison" <jarrodm@ihug.c om.au> wrote in message
> news:bvqfpj$c3p $1@lust.ihug.co .nz...[color=green]
> > Hi Simon
> >
> > Thanks for the link, it did explain what i was trying to do but im not[/color]
> sure[color=green]
> > if im going about it the right way, ive posted below the procedure im[/color]
> using[color=green]
> > and it works correctly under sql query analyzer but not in VB, im[/color][/color]
assuming[color=blue][color=green]
> > that this is because im using a temp table and then deleting the temp[/color]
> table[color=green]
> > afterwards. The reason im using a temp table is because there isnt[/color][/color]
always[color=blue][color=green]
> > 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[/color][/color]
the[color=blue][color=green]
> > search string and deleting each record and finally deleting the table.[/color][/color]
Is[color=blue][color=green]
> > there a better way that i should be doing this ? Thanks for all your[/color][/color]
help[color=blue][color=green]
> >[/color]
>
> <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,[/color]
then[color=blue]
> 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
>
>[/color]

[Jarrod Morrison]

Re: Calling Stored Procedures

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" <sql@hayes.ch > wrote in message
news:40213aa5$1 _1@news.bluewin .ch...[color=blue]
>
> "Jarrod Morrison" <jarrodm@ihug.c om.au> wrote in message
> news:bvqfpj$c3p $1@lust.ihug.co .nz...[color=green]
> > Hi Simon
> >
> > Thanks for the link, it did explain what i was trying to do but im not[/color]
> sure[color=green]
> > if im going about it the right way, ive posted below the procedure im[/color]
> using[color=green]
> > and it works correctly under sql query analyzer but not in VB, im[/color][/color]
assuming[color=blue][color=green]
> > that this is because im using a temp table and then deleting the temp[/color]
> table[color=green]
> > afterwards. The reason im using a temp table is because there isnt[/color][/color]
always[color=blue][color=green]
> > 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[/color][/color]
the[color=blue][color=green]
> > search string and deleting each record and finally deleting the table.[/color][/color]
Is[color=blue][color=green]
> > there a better way that i should be doing this ? Thanks for all your[/color][/color]
help[color=blue][color=green]
> >[/color]
>
> <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,[/color]
then[color=blue]
> 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
>
>[/color]

[Lars Broberg]

Re: Calling Stored Procedures

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" <jarrodm@ihug.c om.au> wrote in message
news:bvt14m$cct $1@lust.ihug.co .nz...[color=blue]
> Hi simon
>
> Just one other quick question, this isnt really important but is there a[/color]
way[color=blue]
> to find out how many records have been returned in the stored procedure[/color]
from[color=blue]
> vb ? If i use the .recordcount function with the object it returns -1
> regardless of how many records there may be.
>
> Thanks
>
>
> "Simon Hayes" <sql@hayes.ch > wrote in message
> news:40213aa5$1 _1@news.bluewin .ch...[color=green]
> >
> > "Jarrod Morrison" <jarrodm@ihug.c om.au> wrote in message
> > news:bvqfpj$c3p $1@lust.ihug.co .nz...[color=darkred]
> > > Hi Simon
> > >
> > > Thanks for the link, it did explain what i was trying to do but im not[/color]
> > sure[color=darkred]
> > > if im going about it the right way, ive posted below the procedure im[/color]
> > using[color=darkred]
> > > and it works correctly under sql query analyzer but not in VB, im[/color][/color]
> assuming[color=green][color=darkred]
> > > that this is because im using a temp table and then deleting the temp[/color]
> > table[color=darkred]
> > > afterwards. The reason im using a temp table is because there isnt[/color][/color]
> always[color=green][color=darkred]
> > > 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[/color][/color]
> the[color=green][color=darkred]
> > > search string and deleting each record and finally deleting the table.[/color][/color]
> Is[color=green][color=darkred]
> > > there a better way that i should be doing this ? Thanks for all your[/color][/color]
> help[color=green][color=darkred]
> > >[/color]
> >
> > <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[/color][/color]
working"[color=blue][color=green]
> > when you run it from VB, and which client library you use. If it's ADO,[/color]
> then[color=green]
> > 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
> >
> >[/color]
>
>[/color]

[Jarrod Morrison]

Re: Calling Stored Procedures

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" <lars.b@elbe-data.nothing.se > wrote in message
news:NyrUb.8164 4$dP1.211699@ne wsc.telia.net.. .[color=blue]
> Jarrod,
> Look at CursorType and CursorLocation in ADO. You are probably using a
> combination which does not give you the recordcount (and then ADO[/color]
indicates[color=blue]
> this by returning -1). ForwardOnly is the default CursorType and it does[/color]
not[color=blue]
> give you the recordcount...
> --
> Lars Broberg
> Elbe-Data AB
> http://www.elbe-data.se
> Remove "nothing." when replying to private e-mail!
>
>
> "Jarrod Morrison" <jarrodm@ihug.c om.au> wrote in message
> news:bvt14m$cct $1@lust.ihug.co .nz...[color=green]
> > Hi simon
> >
> > Just one other quick question, this isnt really important but is there a[/color]
> way[color=green]
> > to find out how many records have been returned in the stored procedure[/color]
> from[color=green]
> > vb ? If i use the .recordcount function with the object it returns -1
> > regardless of how many records there may be.
> >
> > Thanks
> >
> >
> > "Simon Hayes" <sql@hayes.ch > wrote in message
> > news:40213aa5$1 _1@news.bluewin .ch...[color=darkred]
> > >
> > > "Jarrod Morrison" <jarrodm@ihug.c om.au> wrote in message
> > > news:bvqfpj$c3p $1@lust.ihug.co .nz...
> > > > Hi Simon
> > > >
> > > > Thanks for the link, it did explain what i was trying to do but im[/color][/color][/color]
not[color=blue][color=green][color=darkred]
> > > sure
> > > > if im going about it the right way, ive posted below the procedure[/color][/color][/color]
im[color=blue][color=green][color=darkred]
> > > using
> > > > and it works correctly under sql query analyzer but not in VB, im[/color]
> > assuming[color=darkred]
> > > > that this is because im using a temp table and then deleting the[/color][/color][/color]
temp[color=blue][color=green][color=darkred]
> > > table
> > > > afterwards. The reason im using a temp table is because there isnt[/color]
> > always[color=darkred]
> > > > 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[/color][/color][/color]
to[color=blue][color=green]
> > the[color=darkred]
> > > > search string and deleting each record and finally deleting the[/color][/color][/color]
table.[color=blue][color=green]
> > Is[color=darkred]
> > > > there a better way that i should be doing this ? Thanks for all your[/color]
> > help[color=darkred]
> > > >
> > >
> > > <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[/color][/color]
> working"[color=green][color=darkred]
> > > when you run it from VB, and which client library you use. If it's[/color][/color][/color]
ADO,[color=blue][color=green]
> > then[color=darkred]
> > > one common piece of advice is to put SET NOCOUNT ON at the start of[/color][/color][/color]
your[color=blue][color=green][color=darkred]
> > > procedure:
> > >
> > > http://www.aspfaq.com/show.asp?id=2246
> > >
> > > Simon
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

[Erland Sommarskog]

Re: Calling Stored Procedures

Jarrod Morrison (jarrodm@ihug.c om.au) writes:[color=blue]
> 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 ?[/color]

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, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Lars Broberg]

Re: Calling Stored Procedures

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" <sommar@algonet .se> wrote in message
news:Xns948881D 4E980EYazorman@ 127.0.0.1...[color=blue]
> Jarrod Morrison (jarrodm@ihug.c om.au) writes:[color=green]
> > 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 ?[/color]
>
> 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, sommar@algonet. se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp[/color]