August 30, 2007 at 8:52 am
I Have 2 store procs below where am trying to Add/Edit records through XML. I want to add/edit multiple jobid for single empid. CAn anyone pls check if my procs doing OK.
Proc1
alter PROCEDURE [dbo].[AddEmp]
(
@EmpID int out,@EmpName varchar(100), @JobID text)
As
begin
declare
@XMLdoc int
BEGIN
TRANSACTION addemp
insert into tbEmp( empname) values (@Empname)
EXEC
sp_xml_preparedocument @Idoc OUTPUT, @JobID
INSERT
INTO tbempjob (EmpId,JobID)
SELECT
@EmpId,JobID
FROM OPENXML (@XMLdoc, '/Jobs/Item', 1)
WITH (JobID int)
EXEC
sp_xml_removedocument @XML doc
COMMIT
TRANSACTION addemp
end
August 30, 2007 at 9:32 am
Let me know if anybody has questions.
August 30, 2007 at 10:31 am
I am trying to do something like this for Proc 1
execute
Addemp '<jobs><Item JobID = "3" JobID = "2"></Item></jobs>'
August 30, 2007 at 10:32 am
I think that what most of us are waiting for is for you to tell us what happens when you run it, etc.
August 30, 2007 at 10:43 am
when i do tht it gives me error
Msg 8114, Level 16, State 4, Procedure spAddEmp, Line 0
Error converting data type varchar to int.
How can I insert multiple jobid for 1 empid without reentering the empid again.
say I want to insert
empid=4, jobid =1
empid=4,jobid=2
empid=4,jobid=3
I wud like to insert empid only once and jobid multiple for tht
August 30, 2007 at 11:59 am
I still get the same error though my proc looks fine for me. may be execting it is a problem
Msg 8114, Level 16, State 4, Procedure spAddEmp, Line 0
Error converting data type varchar to int.
August 30, 2007 at 1:47 pm
Use
execute spAddOpptest @OppName=...
Otherwise it tries to fill the first parameter @OppID int out with your text
It is better to explictly mention the parameternames in case the order of parameters change
August 30, 2007 at 2:03 pm
tried exec with all the parameters but still the same error
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply