473,394 Members | 1,956 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,394 software developers and data experts.

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

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
Jun 27 '08 #1
3 4125
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
Jun 27 '08 #2
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

Jun 27 '08 #3
>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?
Jun 27 '08 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Scott Natwick | last post by:
Hi, Is there a way to obtain the return code from a stored procedure? Here is what I have so far. The procedure executes, but I'm not able to find the return code from the procedure. ...
8
by: Peter | last post by:
Hi, there I have created an stored procedure using the DDL below for my MS Access Database and no error occurs. Also it can create an stored procedure if I changed the parameter from "" to...
5
by: Sandy | last post by:
Hello - I need a good example of how to take a return value from a stored procedure and use it in vb code. I have an app that searches a database by city and state. If a user makes a typo, the...
2
by: Hardik Shah | last post by:
Hi, I have created a sql stored procedure which returns two values. it runs successfully from query analyser. I want to run it from my vb.net code , it runs without any error but it don't store...
1
by: jkeel | last post by:
If I try to Update a record with the following code using a stored procedure I get an error: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$...
4
by: =?Utf-8?B?QmFidU1hbg==?= | last post by:
Hi, I have a GridView and a SqlDataSource controls on a page. The SqlDataSource object uses stored procedures to do the CRUD operations. The DataSource has three columns one of which -...
1
by: mazdotnet | last post by:
Hi guys, I can't figure out why this is not working? I need to display all the rows for a given query for a given page index (ex. row 10..20) and the total number of rows. I got the first part...
4
by: jleeie | last post by:
Can someone help me, I'm going round in circles with this and my head is cabbaged ! I am using visual studio 2005 & VB & MS SQL 2005 I am trying to execute a stored procedure from within a...
7
by: E11esar | last post by:
Hi there. I have written a C# web service that calls an Oracle stored procedure. The SP is a simple select-max query and the table it is getting the value from has about 2.8 million rows in it. ...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.