Returning a value from a Stored Proc to a Stored Proc 
Author Message
 Returning a value from a Stored Proc to a Stored Proc

The situation:

I have a stored procedure for our employees database that inserts a new
employee. This procedure executes a SQL query to find the new employee's id
number.

The 'insert new employee' procedure is called by another stored procedure.
In order to do finish the processing in this procedure, I need to have the
employee's id number, which is found in the first stored procedure.

For some reason, I can't figure out a pass the value of the employee's id
number between the procedures.

The following is my code for the AddNewEmployee procedure. I try to specify
the new Employee Number as OUTPUT, but I can't figure out how to access that
value from the procedure that calls this procedure.

Thanks in advance for the help.

CREATE PROCEDURE [AddNewEmployee]

...
...
...


AS

BEGIN TRAN


 Begin
  Print 'Please provide the candidate number'
  Return --Breaks out of stored procedure
 End



 BEGIN
  ROLLBACK TRAN
  RETURN
 END

insert into employees (
 employee_number,
...
...
...
 billable
 ) values (

...
...
...

 )

 BEGIN
  ROLLBACK TRAN
  RETURN
 END

COMMIT TRAN

Return



Sun, 23 Feb 2003 22:35:04 GMT
 Returning a value from a Stored Proc to a Stored Proc

create an ouput parameter

Quote:
> The situation:

> I have a stored procedure for our employees database that inserts a new
> employee. This procedure executes a SQL query to find the new employee's
id
> number.

> The 'insert new employee' procedure is called by another stored procedure.
> In order to do finish the processing in this procedure, I need to have the
> employee's id number, which is found in the first stored procedure.

> For some reason, I can't figure out a pass the value of the employee's id
> number between the procedures.

> The following is my code for the AddNewEmployee procedure. I try to
specify
> the new Employee Number as OUTPUT, but I can't figure out how to access
that
> value from the procedure that calls this procedure.

> Thanks in advance for the help.

> CREATE PROCEDURE [AddNewEmployee]

> ...
> ...
> ...


> AS

> BEGIN TRAN


>  Begin
>   Print 'Please provide the candidate number'
>   Return --Breaks out of stored procedure
>  End



>  BEGIN
>   ROLLBACK TRAN
>   RETURN
>  END

> insert into employees (
>  employee_number,
> ...
> ...
> ...
>  billable
>  ) values (

> ...
> ...
> ...

>  )

>  BEGIN
>   ROLLBACK TRAN
>   RETURN
>  END

> COMMIT TRAN

> Return



Sun, 23 Feb 2003 22:47:41 GMT
 Returning a value from a Stored Proc to a Stored Proc
Doug,

Here's something to give you an idea on how to catch the OUT:


EXEC AddNewEmployee

 ...

--
Tibor Karaszi, SQL Server MVP
Please reply to the newsgroup only, not by email.



Sun, 23 Feb 2003 22:47:07 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Stored Proc Calling Another Stored Proc

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

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

4. calling stored proc from stored proc via variable

5. Calling a stored proc within a stored proc

6. Calling Stored Proc from other Stored Proc

7. Exec store proc within a store proc

8. a stored proc calling another stored proc

9. Executing a stored proc in a stored proc...

10. creating a stored proc from inside another stored proc

11. Calling another stored proc within a stored proc

12. Using a stored proc to call another stored proc


 
Powered by phpBB® Forum Software