select.....Insert into store procedure !

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

select.....Insert into store procedure !

Author

Topic

M2
Starting Member

22 Posts

Posted - 2002-06-11 : 04:01:44

I have:

Store Procedure: sp_InsertEmployeeDetail
Table: tbl_Staff

How could I insert all data which located inside table:tbl_Staff by using sp_InsertEmployeeDetail ?

Insert into exec dbo.sp_InsertEmployeeDetail
(@EmpNo,@ICNo,@Old_ICNo,@JoinDate,@Name,@DOB)
Select EmpID,NewIC,OldIC,[Hire Date],[Name],[Birth Date] from tbl_Staff

Someone please provide some guidance for me !

dataphile
Yak Posting Veteran

71 Posts

Posted - 2002-06-11 : 04:20:55

create procedure sp_InsertEmployeeDetail
(@EmpNo int,@ICNo int,@Old_ICNo int,@JoinDate datetime,@Name varchar,@DOB datetime)
as insert into tbl_Staff(
EmpID,NewIC,OldIC,[Hire Date],[Name],[Birth Date])
values(@EmpNo,@ICNo,@Old_ICNo,@JoinDate,@Name,@DOB)

exec sp_InsertEmployeeDetail(1,1,1,'2002/01/01','Pete','1987/12/20')

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-06-11 : 04:23:55

You can use a cursor to generate the parameters for the stored proc. Something like:

DECLARE Emp_cursor CURSOR
FOR SELECT EmpID,NewIC,OldIC,[Hire Date],[Name],[Birth Date]
FROM tbl_Staff
OPEN Emp_cursor

FETCH NEXT FROM Emp_cursor INTO @EmpNo, @ICNo, @Old_ICNo, @JoinDate, @Name, @DOB

WHILE @@FETCH_STATUS = 0
BEGIN
exec dbo.sp_InsertEmployeeDetail @EmpNo, @ICNo, @Old_ICNo, @JoinDate, @Name, @DOB

FETCH NEXT FROM Emp_cursor INTO @EmpNo, @ICNo, @Old_ICNo, @JoinDate, @Name, @DOB
END

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-11 : 08:58:12

I think dataphile and YellowBug are doing a respectable job interpreting your question, but I believe you really need to take a look at what you are saying....

You CANNOT insert data into a stored procedure. You CAN insert into a table/view and you CAN execute a stored procedure. The distinction is very important as an INSERT statement is a set based construct while the EXECUTE (aside from the dml contained within) is not . . .

Now, second, I highly doubt there is a legitamate reason to iterate through the records one by one and running a proc called sp_InsertEmployeeDetail for each row. If you post the DML contained within sp_InsertEmployeeDetail, I bet we can show you a setbased solution to your problem that will perform much better...

<O>

M2
Starting Member

22 Posts

Posted - 2002-06-12 : 21:50:49

The following is the code within sp_InsertEmployeeDetail
---------------------------------------------------------

CREATE PROCEDURE sp_InsertEmployeeDetail

@Name varchar(50),
@ICNo char(14),
@EmpNo varchar(5),
@JoinDate datetime

AS

Declare @MyIdent1 int
Declare @MyIdent2 int

Exec sp_InsertPerson
@Name = @Name,
@ICNo = @ICNo,
@PersonID = @MyIdent2 OUTPUT

Select @MyIdent2 As PersonNewID

Exec sp_InsertEmployee
@EmpNo = @EmpNo,
@PersonID = @MyIdent2,
@JoinDate = @JoinDate,
@EmpID = @MyIdent1 OUTPUT

Select @MyIdent1 As IdentityValue

Select * from tbl_Employee a left outer join tbl_Person p on a.PersonID = p.PersonID
Where EmpID = @MyIdent1
GO

Please teach me what should I do?
Thank You!

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-06-12 : 22:57:09

please post the code for sp_InsertPerson, sp_InsertEmployee and the columns in tbl_Staff then we will be able to write a stored procedure that does what InsertPerson and InsertEmployee by using tbl_Staff.

MakeYourDaddyProud

184 Posts

Posted - 2002-06-13 : 04:16:54

quote:

I think dataphile and YellowBug are doing a respectable job interpreting your question, but I believe you really need to take a look at what you are saying....

You CANNOT insert data into a stored procedure. You CAN insert into a table/view and you CAN execute a stored procedure. The distinction is very important as an INSERT statement is a set based construct while the EXECUTE (aside from the dml contained within) is not . . .

Now, second, I highly doubt there is a legitamate reason to iterate through the records one by one and running a proc called sp_InsertEmployeeDetail for each row. If you post the DML contained within sp_InsertEmployeeDetail, I bet we can show you a setbased solution to your problem that will perform much better...

<O>

I agree with 47, it's good to know there are some amusing threads here!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources