Calling another stored proc within a stored proc 
Author Message
 Calling another stored proc within a stored proc

-- SaveAddress procedure:
-- *************************************************
 CREATE PROCEDURE prc_SaveAddress





 AS
 BEGIN


    BEGIN TRANSACTION
    INSERT INTO Address (Street, ZipCode, City, State, DatetimeAdded,
 DatetimeUpdated)



       BEGIN

             BEGIN
                ROLLBACK TRANSACTION
             END
       END
    ELSE
       BEGIN

          COMMIT TRANSACTION
       END

 END
 *********************************************

-- Snippet of code from SaveEmployee procedure:
-- ******************************************



-- ***********************************


Quote:
> Hi folks,

> I have an address table and a stored proc that takes in street, city,
> etc., inserts these values into the table and returns the addressId
> (which is an identity column).
> Let's call this stored proc "SaveAddress"

> I also have an employee table with columns like name, phone number,
> etc. and also an addressId.
> Let's call the stored proc that saves employee details "SaveEmployee".

> Now, I wish to pass employee name, phone number, street, city, etc. to
> SaveEmployee. Within SaveEmployee, I will call SaveAddress by giving it
> the relevant parameters, get back an addressId, and insert that
> addressId into the employee table.

> For some reason, the addressId that I get back is always a zero,
> although I've checked the address table and found that it has a valid
> address row.

> I've pasted my code below.

> SaveAddress procedure:
> *************************************************
> CREATE PROCEDURE prc_SaveAddress




> AS
> BEGIN



>    BEGIN TRANSACTION
>    INSERT INTO Address (Street, ZipCode, City, State, DatetimeAdded,
> DatetimeUpdated)



>       BEGIN

>             BEGIN
>                ROLLBACK TRANSACTION
>             END
>       END
>    ELSE
>       BEGIN


>          COMMIT TRANSACTION
>       END

> END
> *********************************************

> Snippet of code from SaveEmployee procedure:
> ******************************************


> -- now insert values into Employee

> INSERT INTO Employee (EmployeeLastName, AddressId, PhoneNumber)

> ***********************************

> Could anybody let me know what I need to do???

> Thanks,
> Jay

> PS: Cutting and pasting the code from the SaveAddress procedure into
> the SaveEmployee procedure is not a good solution for me :)

> Sent via Deja.com http://www.***.com/
> Before you buy.



Sat, 17 Aug 2002 03:00:00 GMT
 Calling another stored proc within a stored proc

Hi folks,

I have an address table and a stored proc that takes in street, city,
etc., inserts these values into the table and returns the addressId
(which is an identity column).
Let's call this stored proc "SaveAddress"

I also have an employee table with columns like name, phone number,
etc. and also an addressId.
Let's call the stored proc that saves employee details "SaveEmployee".

Now, I wish to pass employee name, phone number, street, city, etc. to
SaveEmployee. Within SaveEmployee, I will call SaveAddress by giving it
the relevant parameters, get back an addressId, and insert that
addressId into the employee table.

For some reason, the addressId that I get back is always a zero,
although I've checked the address table and found that it has a valid
address row.

I've pasted my code below.

SaveAddress procedure:
*************************************************
CREATE PROCEDURE prc_SaveAddress




AS
BEGIN



   BEGIN TRANSACTION
   INSERT INTO Address (Street, ZipCode, City, State, DatetimeAdded,
DatetimeUpdated)



      BEGIN

            BEGIN
               ROLLBACK TRANSACTION
            END
      END
   ELSE
      BEGIN


         COMMIT TRANSACTION
      END

END
*********************************************

Snippet of code from SaveEmployee procedure:
******************************************

-- now insert values into Employee

INSERT INTO Employee (EmployeeLastName, AddressId, PhoneNumber)

***********************************

Could anybody let me know what I need to do???

Thanks,
Jay

PS: Cutting and pasting the code from the SaveAddress procedure into
the SaveEmployee procedure is not a good solution for me :)

Sent via Deja.com http://www.deja.com/
Before you buy.



Sun, 18 Aug 2002 03:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Calling a stored proc within a stored proc

2. Calling a Stored proc within a stored proc

3. Need Help on Dynamic Stored Proc Call within Stored Proc w/OUTPUT

4. How to get results from Stored Proc within a Stored Proc

5. Exec store proc within a store proc

6. populating a temptable from a stored proc within a store proc

7. newbie trying to execute a stored proc from within a stored proc

8. Create large Stored proc from within stored proc

9. Stored Proc within a Stored Proc

10. Stored Proc Calling Another Stored Proc

11. How to call a stored Proc or Ext Stored proc /T-SQL UDF from VBScript

12. calling stored proc from stored proc via variable


 
Powered by phpBB® Forum Software