SQL Server Performance

xml in storedprocedure

Discussion in 'T-SQL Performance Tuning for Developers' started by Rakeshkv, Dec 1, 2005.

  1. Rakeshkv New Member

    hi,

    i have 2 tables. i have to insert data in to one table by comparing the data with second table.

    how to do this with storedprocedure and the input to the storedprocedure is xmlstring.

    the querry have written is

    alter procedure insertProcedure
    (
    @xmlString varchar(500),
    @DeptID int output
    )
    as
    DECLARE @idoc int

    DECLARE @GUID uniqueidentifier
    SET @GUID = NEWID()

    DECLARE @DID int
    set @DID = @DeptID
    EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlString

    select @DeptID = DeptID from Department where DeptName = @DeptName


    insert into Employee (EmpId,EmpName,DeptID,Age)
    SELECT *
    FROM OPENXML (@idoc, '/Employee',2)
    with (EmpID uniqueidentifier, EmpName varchar(255),DeptID int,Age int)
    go


    insert into Employee (EmpId,EmpName,DeptID,Age) values (@GUID,@EmpName,@DeptID,@Age)

    EXEC sp_xml_removedocument @idoc
    go


    can anyone help me in figuring out the problem.
  2. vbkenya New Member

    A quick scan of the code above [since you did not povide any error messages or the actual problem] will reveal that the following lines


    quote:
    insert into Employee (EmpId,EmpName,DeptID,Age) values (@GUID,@EmpName,@DeptID,@Age)

    EXEC sp_xml_removedocument @idoc

    will not compile/run because the variables @GUID,@EmpName,@DeptID,@Age and @idoc are scoped only within the stored procedure insertprocedure and are not available to the insert statement nor to the line invoking the sp_xml_removedocument.

    These 2 lines should appear before the GO statement at the end of the stored procedure definition if that is the intended logic. Otherwise, it would be nice if you provided the actual problem/error.


    Nathan H. Omukwenyi

Share This Page