How to return a Pk value from one stored procedure to another storedprocedure

[gopi2ks]

Dear All,

I have one stored procedure like sp_insertEmployee

Employee Table Fileds

Eno int pk,
ename varchar(100),
designation varchar

In stored Procedure After inserting the ename and designation it has
to return the Eno pk Id automatically

I have another Department

deptno int pk,
Eno int fk,
Depname varchar

In this stored procedure I need to execute the sp_insertEmployee
Stored procedure and we need that Pk return value after executing
that

By using that Id in this Department table we will insert the eno

can u help me out on this issue

Thanks

[Eric]

CREATE PROCEDURE sp_insertEmployee
(
@Ename VARCHAR(100),
@Designation VARCHAR(100), --Length?
@Eno INT OUTPUT
)
AS

BEGIN --Procedure
INSERT Employee
(
Ename VARCHAR(100),
Designation VARCHAR(100) --??
)
VALUES
(
@Ename,
@Designation
)

SET @Eno = SCOPE_IDENTITY()

END --Procedure
------------------------------

When you execute this procedure from the procedure that inserts the
record in the cross-reference table between department and employee,
call it like so.

EXEC sp_insertEmployee @Ename, @Designation, @Eno OUTPUT
Alternately, you can also have the sproc RETURN the @Eno, instead of
having it as an OUTPUT parameter.

CREATE PROCEDURE sp_insertEmployee
(
@Ename VARCHAR(100),
@Designation VARCHAR(100) --Length?
)
AS

BEGIN --Procedure
INSERT Employee
(
Ename VARCHAR(100),
Designation VARCHAR(100) --??
)
VALUES
(
@Ename,
@Designation
)

RETURN SCOPE_IDENTITY()

END --Procedure

--------------

And to execute it you would do the following from the other sproc...

EXEC @Eno = sp_insertEmployee @Ename, @Designation
Also, it used to be the case that stored procedures named with sp_
were reserved. It was an issue with SQL Server 2000. I don't know if
that still a concern with SQL Server 2005, as I just don't do it
anymore. You might want to verify that it's not still an issue if you
stick with this sp_ prefix as your naming convention. My hunch is
that it still is a performance issue that will cause your sproc to be
recompiled on every execute. Here's an article on the history. I
can't imagine that SQL Server wouldn't still take advantage of the
performance gain for it's own system stored procedures by continuing
to make this assumption.

http://www.sqlmag.com/Articles/Artic...3011.html?Ad=1

Yes, apparently it's still an issue in MS SQL Server 2005 from this
source:

http://www.codeattest.com/blogs/mart...edures-is.html

[Plamen Ratchev]

In addition to use SCOPE_IDENTITY() on SQL Server 2005 you can use the
OUTPUT clause to return the inserted values.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[--CELKO--]

>I have one stored procedure like sp_insertEmployee <<

Why did you use the "sp_" prefix? It has special meaning in SQL
Server. Why did you use camelCase; it is so hard to read that even
Microsoft gave up on it.

Why did you post narrative instead of real DDL? Columns are not
fields. Why did you use a singular table name instead of a collective
or plural name? Have you ever seen a person with a name that is 100
characters long? If you allow it, you will! VARCHAR means VARCHAR(1)
which means CHAR(1). Designastion is too vague to be a data element
name. Is this what you meant to post?

CREATE TABLE Personnel
(emp_nbr INTEGER NOT NULL PRIMARY KEY,
emp_name VARCHAR(35) NOT NULL, --USPS standard
foobar_designation CHAR(1) NOT NULL);

>In stored procedure after inserting the emp_name and designation it has to return the emp_nbr automatically <<

No, that is not how RDBMS works. You are supposed to know what the
key is BEFORE insertion into the database. Do you own an automobile?
The VIN is on the car when you buy it because the VIN is a true
relational key.

I hope that you are not so bad a programmer that you think some
proprietary auto-increment feature will give you a key!

Once you have a way to get employee identifiers that can be validated
and verified, why don't you insert the data into both tables in one
procedure?