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]

"Jarrod Morrison" <ja*****@ihug.com.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

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]

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.com.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

[Erland Sommarskog]

Chuck Conover (cc******@commspeed.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

[Trevor Best]

On Mon, 2 Feb 2004 23:13:55 +0000 (UTC) in
comp.databases.ms-sqlserver, Erland Sommarskog <so****@algonet.se>
wrote:

Chuck Conover (cc******@commspeed.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_myproc, 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]

Trevor Best (bouncer@localhost) 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_myproc, 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

[Jarrod Morrison]

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].[EntityLauncherItems]

@UserName VarChar(50),
@MachineName VarChar (50),
@EntityLocationID 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_MachineAssoc WHERE
MachineName = @MachineName

/* SELECT Group Machine Items */

INSERT INTO #Id (AppId) SELECT AppID FROM Launcher_LocationAssoc WHERE
LocationID = @EntityLocationId

/* SELECT UserName Items */

INSERT INTO #Id (AppId) SELECT AppId FROM Launcher_UserAssoc 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.google.c om...

"Jarrod Morrison" <ja*****@ihug.com.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

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

[Simon Hayes]

"Jarrod Morrison" <ja*****@ihug.com.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

[Erland Sommarskog]

Jarrod Morrison (ja*****@ihug.com.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

[Jarrod Morrison]

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.com.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

[Jarrod Morrison]

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.com.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

[Lars Broberg]

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.com.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.com.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

[Jarrod Morrison]

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********************@newsc.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.com.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.com.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

[Erland Sommarskog]

Jarrod Morrison (ja*****@ihug.com.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

[Lars Broberg]

Jarrod,
As Erland said, but beware that you will get a "disconnected" 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
adLockPessimistic, adLockOptimistic or adLockBatchOptimistic.
--
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.com.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

[Jarrod Morrison]

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.ActiveConnection = CPDBase
PtrlCmd.CommandText = "sp_EntityMemberShips"
PtrlCmd.CommandType = adCmdStoredProc
PtrlRst.CursorType = adOpenStatic
PtrlRst.CursorLocation = adUseClient
PtrlCmd.Parameters.Append PtrlCmd.CreateParameter("MachineName", adVarChar,
adParamInput, 50, frmLoading.lblMachine)
PtrlCmd.Parameters.Append PtrlCmd.CreateParameter("UserName", adVarChar,
adParamInput, 50, frmLoading.lblUserName)

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.com.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

[Erland Sommarskog]

Jarrod Morrison (ja*****@ihug.com.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.ActiveConnection = CPDBase
PtrlCmd.CommandText = "sp_EntityMemberShips"
PtrlCmd.CommandType = adCmdStoredProc
PtrlRst.CursorType = adOpenStatic
PtrlRst.CursorLocation = 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

[Erland Sommarskog]

Erland Sommarskog (so****@algonet.se) writes:

Jarrod Morrison (ja*****@ihug.com.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.ActiveConnection = CPDBase
PtrlCmd.CommandText = "sp_EntityMemberShips"
PtrlCmd.CommandType = adCmdStoredProc
PtrlRst.CursorType = adOpenStatic
PtrlRst.CursorLocation = 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

[Jarrod Morrison]

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*********************@127.0.0.1...

Erland Sommarskog (so****@algonet.se) writes:

Jarrod Morrison (ja*****@ihug.com.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.ActiveConnection = CPDBase
PtrlCmd.CommandText = "sp_EntityMemberShips"
PtrlCmd.CommandType = adCmdStoredProc
PtrlRst.CursorType = adOpenStatic
PtrlRst.CursorLocation = 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