Sql Server Stored procedure Query

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

Hi All,

I have a table T1 with column EMPID as primary key with identity specification.

I have another table T2 with DEPT ID as primary key with identity specification and EMPID as Foreign key.

I have data in T1 like empid=24.

Now I want to insert the data into T2 using stored procedures.

I want EMPID value 24 to be inserted automatically while i am inserting values to T2 using stored procedure.

Can anyone help me regarding this.

Thank you,

Posted 25-May-12 1:02am

jaipal0908

Updated 25-May-12 1:04am

RaisKazi

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2012-05-25T01:36:00

First of all, follow this link: Creating Stored Procedures[^]

The below procedure selects employee by his/her FisrtName and LastName. If employee does not exists, adding data into Employee table, then adding data into Employee_Detail table.

SQL

CREATE STORED PROCEDURE AddEmpIntoDept
    @LastName NVARCHAR(50), 
    @FirstName NVARCHAR(50),
    @DepId INT
AS 
BEGIN
    DECLARE @empid INT
    DECLARE @retval INT

    SET NOCOUNT ON;
    SELECT @empid = ISNULL(EmpId,0), FirstName, LastName
    FROM Employees
    WHERE FirstName = @FirstName AND LastName = @LastName;

    IF  @empid = 0 
    BEGIN
        INSERT INTO Employess (FirstNam, LastName)
            VALUES (@FirstName, @LastName)
        SET @empid = @@IDENTITY
    END

    INSERT INTO Employees_Detail (EmpId, DeptId)
        VALUES(@DepId, @empid)

    SET @retVal = @@IDENTITY
   
    RETURN @retval
END
GO

abhijeetgupta1988 · Answer 2 · 2012-05-25T01:06:00

Solution 1

After you insert into T1 use

@@identity

value to send new record into T2 table as foreign key

Posted 25-May-12 1:06am

abhijeetgupta1988

Comments

jaipal0908 25-May-12 7:12am

Thanks Gupta,
Can u post stored procedure to insert into T2 so that i can understand.
T1-->empid(pk)
T2-->deptid(pk with identity spec),empid(fk).

Thank you,

abhijeetgupta1988 25-May-12 7:23am

create proc spInsert
(
@empname varchar(30)
)
as
begin

--First Insert
insert into T1
select
@empname

--Second Insert Begin
Select @fkEmpId =@@IDENTITY

insert into T2
(
fkEmpId
)
end

abhijeetgupta1988 25-May-12 7:26am

I'm assuming EmpId & Deptid are Identity and Identity Incremented

TenmanS14 · Answer 3 · 2012-05-25T01:35:00

Solution 2

might be better using Scope_Identity in 2008 don't think it'd matter for this particular case though.

see here..

http://msdn.microsoft.com/en-us/library/ms190315.aspx[^]

Posted 25-May-12 1:35am

TenmanS14