Hi,

I am trying to execute two child stored procedures from a parent one. The 
first stored procedure inserts a new company into Companies table and returns 
its CompanyID, the second stored procedure inserts an address into Addresses 
table and returns its AddressID and the third one assigns the address to the 
company.

Everything works fine but when I run the parent stored procedure, I only 
want to return the CompanyID back to my application. However, the code below 
returns both CompanyID and AddressID.

I'm using OUTPUT clause so that I can grab CompanyID and AddressID returned 
by first and second child stored procedures respectively. I think because of 
that the parent stored procedure returns both IDs. However, if I don't use 
the OUTPUT clause, I can't grab the CompanyID and AddressID values returned 
by the first and second child procedures.

By the way, both CompayID and AddressID are defined as OUTPUT parameters in 
those child procedures.

Here's the spInsertCompany stored procedure:



CREATE spInsertCompany
   @CompanyID int OUTPUT,
   @CompanyName varchar(50)
AS
   -- Insert new company into Companies table
   INSERT INTO Companies
	(CompanyName)
   VALUES
	(@CompanyName)

   -- Grab CompanyID
   SET @CompanyID = SCOPE_IDENTITY()

   -- Return CompanyID
   SELECT @CompanyID AS CompanyID


Is there another way for me to grab IDs returned by child procedures without 
using the OUTPUT clause so that my parent procedure returns only the 
CompanyID?

Here's what my parent procedure looks like:


CREATE spTestProcedure
   @iCompanyName varchar(50),
   @iAddress1 varchar(100),
   @iAddress2 varchar(100),
   @iCity varchar(50),
   @iState char(2),
   @iZipCode varchar(10)
AS
   -- Step 1: Insert company into Companies table
   DECLARE @iCompanyID int
   EXEC spInserCompany @CompanyID = @iCompanyID OUTPUT, @CompanyName = 
@iCompanyName

   -- Step 2: Insert address into Addresses table
   DECLARE @iAddressID int
   EXEC spInsertAddress @AddressID = @iAddressID OUTPUT, @Address1 = 
@iAddress1, @City = @iCity, @State = @iState, @ZipCode = @iZipCode

   -- Step 3: Assign address to company
   EXEC spAssignAddressToCompany @CompanyID = @iCompanyID, @AddressID = 
@iAddressID

   -- Step 4: Return CompanyID
   -- I only want to return the CompanyID but when I run the stored 
procedure, I get both CompanyID and AddressID at this point 
-- 
Thanks,

Sam

Sam (Sam@discussions.microsoft.com) writes:
> I am trying to execute two child stored procedures from a parent one.
> The first stored procedure inserts a new company into Companies table
> and returns its CompanyID, the second stored procedure inserts an
> address into Addresses table and returns its AddressID and the third one
> assigns the address to the company. 
> 
> Everything works fine but when I run the parent stored procedure, I only
> want to return the CompanyID back to my application. However, the code
> below returns both CompanyID and AddressID. 
> 
> I'm using OUTPUT clause so that I can grab CompanyID and AddressID
> returned by first and second child stored procedures respectively. I
> think because of that the parent stored procedure returns both IDs.
> However, if I don't use the OUTPUT clause, I can't grab the CompanyID
> and AddressID values returned by the first and second child procedures. 

It's not entirely clear what you really want to achieve, but:

> CREATE spInsertCompany
>    @CompanyID int OUTPUT,
>    @CompanyName varchar(50)
> AS
>    -- Insert new company into Companies table
>    INSERT INTO Companies
>      (CompanyName)
>    VALUES
>      (@CompanyName)
> 
>    -- Grab CompanyID
>    SET @CompanyID = SCOPE_IDENTITY()
> 
>    -- Return CompanyID
>    SELECT @CompanyID AS CompanyID

There you produce a result set that you return to the client. If the
intention is that you should only return company id as an OUTPUT parameter,
you should delete this last line.
 



-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

On Mon, 1 Mar 2010 13:21:02 -0800, Sam <Sam@discussions.microsoft.com>
wrote:

>Hi,
>
>I am trying to execute two child stored procedures from a parent one. The 
>first stored procedure inserts a new company into Companies table and returns 
>its CompanyID, the second stored procedure inserts an address into Addresses 
>table and returns its AddressID and the third one assigns the address to the 
>company.
>
>Everything works fine but when I run the parent stored procedure, I only 
>want to return the CompanyID back to my application. However, the code below 
>returns both CompanyID and AddressID.
>
>I'm using OUTPUT clause so that I can grab CompanyID and AddressID returned 
>by first and second child stored procedures respectively. I think because of 
>that the parent stored procedure returns both IDs. However, if I don't use 
>the OUTPUT clause, I can't grab the CompanyID and AddressID values returned 
>by the first and second child procedures.
>
>By the way, both CompayID and AddressID are defined as OUTPUT parameters in 
>those child procedures.
>
>Here's the spInsertCompany stored procedure:
>
>
>
>CREATE spInsertCompany
>   @CompanyID int OUTPUT,
>   @CompanyName varchar(50)
>AS
>   -- Insert new company into Companies table
>   INSERT INTO Companies
>	(CompanyName)
>   VALUES
>	(@CompanyName)
>
>   -- Grab CompanyID
>   SET @CompanyID = SCOPE_IDENTITY()
>
>   -- Return CompanyID
>   SELECT @CompanyID AS CompanyID
>
>
>Is there another way for me to grab IDs returned by child procedures without 
>using the OUTPUT clause so that my parent procedure returns only the 
>CompanyID?
>
>Here's what my parent procedure looks like:
>
>
>CREATE spTestProcedure
>   @iCompanyName varchar(50),
>   @iAddress1 varchar(100),
>   @iAddress2 varchar(100),
>   @iCity varchar(50),
>   @iState char(2),
>   @iZipCode varchar(10)
>AS
>   -- Step 1: Insert company into Companies table
>   DECLARE @iCompanyID int
>   EXEC spInserCompany @CompanyID = @iCompanyID OUTPUT, @CompanyName = 
>@iCompanyName
>
>   -- Step 2: Insert address into Addresses table
>   DECLARE @iAddressID int
>   EXEC spInsertAddress @AddressID = @iAddressID OUTPUT, @Address1 = 
>@iAddress1, @City = @iCity, @State = @iState, @ZipCode = @iZipCode
>
>   -- Step 3: Assign address to company
>   EXEC spAssignAddressToCompany @CompanyID = @iCompanyID, @AddressID = 
>@iAddressID
>
>   -- Step 4: Return CompanyID
>   -- I only want to return the CompanyID but when I run the stored 
>procedure, I get both CompanyID and AddressID at this point 


Isn't CompanyId a GUID? :)

John

Here's what I'm trying to do:

I have a stored procedure that inserts new companies into the database. When 
it does that it returns the CompanyIDs assigned to them.

I also have a seperate stored procedure that enters addresses into the 
database and returns their AddressIDs.

Individually, these stored procedure serve their own purposes.

However, I also wanted to have a form in my application that allows the user 
to enter a new company along with its address in one shot but I didn't want 
to design another stored procedure that I have to maintain seperately. So I 
decided to create a "parent stored procedure" that takes advantage of my 
existing stored procedures.

Alternatively, I could've called these stored procedures individually to 
make my company and address entries but that means two database trips which 
is bad for performance.

So, I wrote a "parent' stored procedure that executes both these stored 
procedures with a single hit on the database. For it to work, it needs to 
make the company entry first, get its ID and then make the address entry.

Everything is working fine but the "parent procedure" is returning two 
resultsets i.e. the CompanyID and the AddressID.

This is not a big deal but I'd like to return only the CompanyID back to my 
application. Looks like when I use the OUTPUT clause to grab ID from child 
procedures, that's making the parent procedure to return them as output 
parameters as well.

Hopefully this makes more sense.

-- 
Thanks,

Sam


"Erland Sommarskog" wrote:

> Sam (Sam@discussions.microsoft.com) writes:
> > I am trying to execute two child stored procedures from a parent one.
> > The first stored procedure inserts a new company into Companies table
> > and returns its CompanyID, the second stored procedure inserts an
> > address into Addresses table and returns its AddressID and the third one
> > assigns the address to the company. 
> > 
> > Everything works fine but when I run the parent stored procedure, I only
> > want to return the CompanyID back to my application. However, the code
> > below returns both CompanyID and AddressID. 
> > 
> > I'm using OUTPUT clause so that I can grab CompanyID and AddressID
> > returned by first and second child stored procedures respectively. I
> > think because of that the parent stored procedure returns both IDs.
> > However, if I don't use the OUTPUT clause, I can't grab the CompanyID
> > and AddressID values returned by the first and second child procedures. 
> 
> It's not entirely clear what you really want to achieve, but:
> 
> > CREATE spInsertCompany
> >    @CompanyID int OUTPUT,
> >    @CompanyName varchar(50)
> > AS
> >    -- Insert new company into Companies table
> >    INSERT INTO Companies
> >      (CompanyName)
> >    VALUES
> >      (@CompanyName)
> > 
> >    -- Grab CompanyID
> >    SET @CompanyID = SCOPE_IDENTITY()
> > 
> >    -- Return CompanyID
> >    SELECT @CompanyID AS CompanyID
> 
> There you produce a result set that you return to the client. If the
> intention is that you should only return company id as an OUTPUT parameter,
> you should delete this last line.
>  
> 
> 
> 
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
> 
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> 
> .
> 

Returning a value with an OUTPUT parameter and returning a query are two 
different processes that are totally unrelated.  You don't need to make a 
Select query to return an Output parameter value and you don't need to 
declare a parameter as OUTPUT to return a value using a Select query.

At this moment, you are chosing both of these two methods to return the 
value of CompanyID but you need only one.

When calling a SP from another SP, it's often (but not always)  simpler to 
use an OUTPUT parameter instead of a Select query but when calling a SP from 
a FORM, often it's the contrary that's true: it will be simpler to use a 
Select Query instead of an Output parameter.  The later is not always true 
and will depend heavily on the platform used and the way the Form is coded; 
however, as you didn't provide any detail about this, it's hard to give you 
any more recommandations.

One way to solve your dilemna could be to add a parameter that will block 
the Select query when the SP is called from another SP.

-- 
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server 
(French)


"Sam" <Sam@discussions.microsoft.com> wrote in message 
news:D5F33DA6-8378-49D3-B902-20C7893E9028@microsoft.com...
> Here's what I'm trying to do:
>
> I have a stored procedure that inserts new companies into the database. 
> When
> it does that it returns the CompanyIDs assigned to them.
>
> I also have a seperate stored procedure that enters addresses into the
> database and returns their AddressIDs.
>
> Individually, these stored procedure serve their own purposes.
>
> However, I also wanted to have a form in my application that allows the 
> user
> to enter a new company along with its address in one shot but I didn't 
> want
> to design another stored procedure that I have to maintain seperately. So 
> I
> decided to create a "parent stored procedure" that takes advantage of my
> existing stored procedures.
>
> Alternatively, I could've called these stored procedures individually to
> make my company and address entries but that means two database trips 
> which
> is bad for performance.
>
> So, I wrote a "parent' stored procedure that executes both these stored
> procedures with a single hit on the database. For it to work, it needs to
> make the company entry first, get its ID and then make the address entry.
>
> Everything is working fine but the "parent procedure" is returning two
> resultsets i.e. the CompanyID and the AddressID.
>
> This is not a big deal but I'd like to return only the CompanyID back to 
> my
> application. Looks like when I use the OUTPUT clause to grab ID from child
> procedures, that's making the parent procedure to return them as output
> parameters as well.
>
> Hopefully this makes more sense.
>
> -- 
> Thanks,
>
> Sam
>
>
> "Erland Sommarskog" wrote:
>
>> Sam (Sam@discussions.microsoft.com) writes:
>> > I am trying to execute two child stored procedures from a parent one.
>> > The first stored procedure inserts a new company into Companies table
>> > and returns its CompanyID, the second stored procedure inserts an
>> > address into Addresses table and returns its AddressID and the third 
>> > one
>> > assigns the address to the company.
>> >
>> > Everything works fine but when I run the parent stored procedure, I 
>> > only
>> > want to return the CompanyID back to my application. However, the code
>> > below returns both CompanyID and AddressID.
>> >
>> > I'm using OUTPUT clause so that I can grab CompanyID and AddressID
>> > returned by first and second child stored procedures respectively. I
>> > think because of that the parent stored procedure returns both IDs.
>> > However, if I don't use the OUTPUT clause, I can't grab the CompanyID
>> > and AddressID values returned by the first and second child procedures.
>>
>> It's not entirely clear what you really want to achieve, but:
>>
>> > CREATE spInsertCompany
>> >    @CompanyID int OUTPUT,
>> >    @CompanyName varchar(50)
>> > AS
>> >    -- Insert new company into Companies table
>> >    INSERT INTO Companies
>> >      (CompanyName)
>> >    VALUES
>> >      (@CompanyName)
>> >
>> >    -- Grab CompanyID
>> >    SET @CompanyID = SCOPE_IDENTITY()
>> >
>> >    -- Return CompanyID
>> >    SELECT @CompanyID AS CompanyID
>>
>> There you produce a result set that you return to the client. If the
>> intention is that you should only return company id as an OUTPUT 
>> parameter,
>> you should delete this last line.
>>
>>
>>
>>
>> -- 
>> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>>
>> Links for SQL Server Books Online:
>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
>> SQL 2000: 
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>>
>> .
>> 

You do not get the two result sets because you use the OUTPUT parameters. It is because of the last SELECT statement you 
have which returns the value as result set. Not sure why you return it both as output parameter and as result set. 
Erland is correct that removing the final SELECT will solve the problem (assuming you do not use the result set elsewhere).

-- 
Plamen Ratchev
http://www.SQLStudio.com

Erland, Sylvain, Plamen,

Thank you very much for your help and detailed explanations. That fixed the 
issue.

This newsgroup still is the best thanks to you guys. I never leave this 
newsgroup empty handed.

Your help and expertise are truly appreciated.
-- 
Thanks,

Sam


"Sam" wrote:

> Here's what I'm trying to do:
> 
> I have a stored procedure that inserts new companies into the database. When 
> it does that it returns the CompanyIDs assigned to them.
> 
> I also have a seperate stored procedure that enters addresses into the 
> database and returns their AddressIDs.
> 
> Individually, these stored procedure serve their own purposes.
> 
> However, I also wanted to have a form in my application that allows the user 
> to enter a new company along with its address in one shot but I didn't want 
> to design another stored procedure that I have to maintain seperately. So I 
> decided to create a "parent stored procedure" that takes advantage of my 
> existing stored procedures.
> 
> Alternatively, I could've called these stored procedures individually to 
> make my company and address entries but that means two database trips which 
> is bad for performance.
> 
> So, I wrote a "parent' stored procedure that executes both these stored 
> procedures with a single hit on the database. For it to work, it needs to 
> make the company entry first, get its ID and then make the address entry.
> 
> Everything is working fine but the "parent procedure" is returning two 
> resultsets i.e. the CompanyID and the AddressID.
> 
> This is not a big deal but I'd like to return only the CompanyID back to my 
> application. Looks like when I use the OUTPUT clause to grab ID from child 
> procedures, that's making the parent procedure to return them as output 
> parameters as well.
> 
> Hopefully this makes more sense.
> 
> -- 
> Thanks,
> 
> Sam
> 
> 
> "Erland Sommarskog" wrote:
> 
> > Sam (Sam@discussions.microsoft.com) writes:
> > > I am trying to execute two child stored procedures from a parent one.
> > > The first stored procedure inserts a new company into Companies table
> > > and returns its CompanyID, the second stored procedure inserts an
> > > address into Addresses table and returns its AddressID and the third one
> > > assigns the address to the company. 
> > > 
> > > Everything works fine but when I run the parent stored procedure, I only
> > > want to return the CompanyID back to my application. However, the code
> > > below returns both CompanyID and AddressID. 
> > > 
> > > I'm using OUTPUT clause so that I can grab CompanyID and AddressID
> > > returned by first and second child stored procedures respectively. I
> > > think because of that the parent stored procedure returns both IDs.
> > > However, if I don't use the OUTPUT clause, I can't grab the CompanyID
> > > and AddressID values returned by the first and second child procedures. 
> > 
> > It's not entirely clear what you really want to achieve, but:
> > 
> > > CREATE spInsertCompany
> > >    @CompanyID int OUTPUT,
> > >    @CompanyName varchar(50)
> > > AS
> > >    -- Insert new company into Companies table
> > >    INSERT INTO Companies
> > >      (CompanyName)
> > >    VALUES
> > >      (@CompanyName)
> > > 
> > >    -- Grab CompanyID
> > >    SET @CompanyID = SCOPE_IDENTITY()
> > > 
> > >    -- Return CompanyID
> > >    SELECT @CompanyID AS CompanyID
> > 
> > There you produce a result set that you return to the client. If the
> > intention is that you should only return company id as an OUTPUT parameter,
> > you should delete this last line.
> >  
> > 
> > 
> > 
> > -- 
> > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
> > 
> > Links for SQL Server Books Online:
> > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> > SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> > 
> > .
> >