August 11, 2003 at 7:49 am
Please this is not a web question.
I am writing a web form input screen that will retrieve an ID from one table and populate that into another table dynamically.
Then I am writing a stored procedure that will actually do the insertion.
Can someone please tell me how that is done?
Normally, I can accomplish that by using these steps:
Create Procedure InsertAccidentRecord(
@empID int,
@EmpName varchar(50))
As
Begin
SELECT @EmpName = Ename
FROM theEmp
WHERE EmpID = @EmpID
THen I would insert the empname into the other table:
INSERT INTO tblMain(
EmpID,
Ename
)
Values(
@EmpID,
@Ename
)
Above code would have inserted eName based on empID.
In the current situation, what I am trying to insert is empId, which means that the select statement would look like this:
SELECT @EmpID = EmpID
FROM theEmp
WHERE EmpID = @EmpID
DOes anyone have any idea how I can handle this?
Thanks in advance
August 11, 2003 at 8:09 am
Not quiet sure what you are saying, but I would put add a trigger on insert.
August 11, 2003 at 9:27 am
Why do you need the EmpName in your first procedure? It's already in table theEmp. It's just redundant data at this point...
--
The procedure doesn't quite make sense...You are passing in the EmpName variable parameter, but then resetting it in the first SELECT statement, then passing it to another INSERT procedure that doesn't even need the variable...Is this what you wanted?:
CREATE PROC dbo.InsertAccident
@EmpID INT
AS
BEGIN
INSERT INTO tblMain
SELECT @EmpID, EName
FROM theEmp
WHERE EmpID = @EmpID
END
GO
August 11, 2003 at 10:26 am
I wasn't actually passing in empName.
I was using that as an example but I do understand what your example is saying.
For instance, if I need to retrieve the value of empid from emp table to be inserted into tblMain, by looking at your example, it would be written like this:
select empid
from emp
where empid = @empid.
If this assumption of what I think you mean is correct, then that will solve my problem.
Thank you!
August 12, 2003 at 7:09 am
quote:
select empidfrom emp
where empid = @empid.
Why not just do an Insert Into Table(EmpID) Values(@EmpID)
The select statement is wasted unnecessary unless you want to insert more than one row, and you are inserting varied data rather than the same thing multiple times, where the empid matches your criteria.
August 12, 2003 at 9:21 am
I have got everything working now the way I want it.
Thanks for your input.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply