>> 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?